What does this code mean?
What does this code mean?
(OP)
Hi, I'm analyzing a program made with MS Access. It has a whole bunch of tables and queries already made, and a form with lots of code behind it. I'm looking through and I'm not sure what the following means:
With CurrentDb
.QueryDefs("qry_cur_pier").SQL = _
" SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME,
RE.AIRPORT_RESOURCE_KEY " & _
" FROM qry_cur_resource AS RE " & _
" WHERE RE.AIRPORT_RESOURCE_CODE = 'PIER';"
qry_cur_pier appears to be an already existing query. AIRPORT_RESOURCE_NAME and AIRPORT_RESOURCE_KEY are fields in that query. AIRPORT_RESOURCE_CODE is not in the query though. What does the RE. and the AS RE do here? Thanks in advance.
With CurrentDb
.QueryDefs("qry_cur_pier").SQL = _
" SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME,
RE.AIRPORT_RESOURCE_KEY " & _
" FROM qry_cur_resource AS RE " & _
" WHERE RE.AIRPORT_RESOURCE_CODE = 'PIER';"
qry_cur_pier appears to be an already existing query. AIRPORT_RESOURCE_NAME and AIRPORT_RESOURCE_KEY are fields in that query. AIRPORT_RESOURCE_CODE is not in the query though. What does the RE. and the AS RE do here? Thanks in advance.





RE: What does this code mean?
This query is selecting fields from the recordset of another query -- qry_cur_resource. In other words, the query 'qry_cur_resource' is executed and a recordset created. This query then acts on that recordset. AIRPORT_RESOURCE_NAME, AIRPORT_RESOURCE_KEY, and AIRPORT_RESOURCE_CODE are all fields in the recordset created by 'qry_cur_resource' query. RE is defined as a alias for that recordset and functions the same as a table name.
RE.AIRPORT_RESOURCE_NAME means the Airport Resource Name field from the RE table. "FROM qry_cur_resource AS RE" means execute the 'qry_cur_resource' query, and name the recordset 'RE'.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: What does this code mean?
"CurrentDB.QueryDefs is a collection object which contains one entry for each defined query, keyed by the name of the query, which in this case, is 'qry_cur_pier'."
What does QueryDefs mean? And why is qry_cur_pier in the parameter and not qry_cur_resource?
I see that AIRPORT_RESOURCE_NAME, AIRPORT_RESOURCE_KEY, and AIRPORT_RESOURCE_CODE are already fields in the qry_cur_resource query, so why use qry_cur_pier at all? qry_cur_resource already exists too right?
The order of the statements is confusing to me. 'SELECT DISTINCT RE.AIRPORT_RESOURCE_NAME' for example is executed before 'FROM qry_cur_resource AS RE'?
Thanks again for your reply. If you have time please help me with these questions.
RE: What does this code mean?
Yes, 'qry_cur_resource' does exist and it too has an entry in QueryDefs. I don't know what the SQL statement for qry_cur_resource actually is, but let's assume it is the following:
SELECT * FROM AIRPORT_TABLE WHERE PROVINCE = 'ON'
If we assume that AIRPORT_TABLE contains records for all the airports in Canada, then this query will extract all the pertinent information for only those airports in Ontario.
Now, when you execute qry_cur_pier, that query will first call qry_cur_resource and create a recordset which contains only those airports in Ontario. The 'AS RE' assigns the alias name 'RE' to that recordset, or intermediate table if you prefer. The main query (qry_cur_pier) will then act against that intermediate query to extract the resource information for those that are piers.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: What does this code mean?
So from my understanding, there are two stages happening here. One is that qry_cur_resource is 'filtered' for preferred information. Another is that qry_cur_pier takes this preferred information, which is now in a table called 'RE', and selects further preferred information. I'm still not sure why qry_cur_pier is needed, but I'll keep thinking about it.
My other concern is about where the RE table is created. I can't seem to find it anywhere in the database. Is it just temporary?
Thanks again, you've been great help.
RE: What does this code mean?
First of all, I'd find out what 'qry_cur_resource' actually does, or what is the SQL for that query. It is also possible that there are a dozen or so queries which all use 'qry_cur_resource' as a base. 'qry_cur_pier' is just one of them, which is based on RESOURCE_CODE, and who knows, others may be based on other attributes.
The RE table is probably just temporary, is built only when qry_cur_resource is executed. There is othing in what we've seen so far to indicate otherwise.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein