If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
When is a Table not a Table?
I've inherited a database and I'm trying to reverse-engineer some of the
design to try and understand how it's put together. I've got this query (below) and while it appears to work fine, it's referencing a table (?) called qryuseddidholdings. Problem is, there is NO table with that name. There IS a query with that name, but it's a different query altogether. Select tbldid800holdings.did, tbldid800holdings.dateassigned, qryuseddidholdings.clientname From tbldid800holdings LEFT JOIN qryuseddidholdings On tbldid800holdings.did = qryuseddidholdings.did Order by tbldid800holdings.did Am I missing something obvious here? Thanks |
#2
|
|||
|
|||
"Hank" wrote in message
... I've inherited a database and I'm trying to reverse-engineer some of the design to try and understand how it's put together. I've got this query (below) and while it appears to work fine, it's referencing a table (?) called qryuseddidholdings. Problem is, there is NO table with that name. There IS a query with that name, but it's a different query altogether. Select tbldid800holdings.did, tbldid800holdings.dateassigned, qryuseddidholdings.clientname From tbldid800holdings LEFT JOIN qryuseddidholdings On tbldid800holdings.did = qryuseddidholdings.did Order by tbldid800holdings.did Am I missing something obvious here? Yes, you're missing the fact that a query can use another query as an input, not just tables. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
With only one or two really picky exceptions, queries can be used anywhere a
table can. I'm not sure I understand what you mean that "it's a different query altogether". What you've posted is not valid SQL for a query named qryuseddidholdings, since it includes a reference to qryuseddidholdings, and Access doesn't allow circular references like that. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Hank" wrote in message ... I've inherited a database and I'm trying to reverse-engineer some of the design to try and understand how it's put together. I've got this query (below) and while it appears to work fine, it's referencing a table (?) called qryuseddidholdings. Problem is, there is NO table with that name. There IS a query with that name, but it's a different query altogether. Select tbldid800holdings.did, tbldid800holdings.dateassigned, qryuseddidholdings.clientname From tbldid800holdings LEFT JOIN qryuseddidholdings On tbldid800holdings.did = qryuseddidholdings.did Order by tbldid800holdings.did Am I missing something obvious here? Thanks |
#4
|
|||
|
|||
Rick Brandt wrote:
Yes, you're missing the fact that a query can use another query as an input, not just tables. I'm aware of that, but if I look at the design view of the combo box and then pull up the Row Source info in Query Builder, it shows the real table, then the qryuseddidholdings "table" with a relationship. So is this a "virtual" table in this sense? And if so, how can it have any data values? Thanks again. |
#5
|
|||
|
|||
Hank wrote:
I've inherited a database and I'm trying to reverse-engineer some of the design to try and understand how it's put together. I've got this query (below) and while it appears to work fine, it's referencing a table (?) called qryuseddidholdings. Problem is, there is NO table with that name. There IS a query with that name, but it's a different query altogether. Select tbldid800holdings.did, tbldid800holdings.dateassigned, qryuseddidholdings.clientname From tbldid800holdings LEFT JOIN qryuseddidholdings On tbldid800holdings.did = qryuseddidholdings.did Order by tbldid800holdings.did; Am I missing something obvious here? Hank, it's perfectly okay to query queries. Just go to the query being queried in the above query and see which objects (tables or queries) it's querying (Whew, that was a mouthful!). Or am I misunderstanding your point? It might help if you clarified what you mean by "It's a different query entirely." LeAnne |
#6
|
|||
|
|||
LeAnne wrote:
it's querying (Whew, that was a mouthful!). Or am I misunderstanding your point? It might help if you clarified what you mean by "It's a different query entirely." Ignore my "it's a different query entirely" statement. Made a mimstake. However, as I mentioned above, when I look at the query in Query Builder, it "looks" like a table and has fields. Is this a "virtual" table? Sorry for my ignorance here. |
#7
|
|||
|
|||
"Hank" wrote in message
... LeAnne wrote: it's querying (Whew, that was a mouthful!). Or am I misunderstanding your point? It might help if you clarified what you mean by "It's a different query entirely." Ignore my "it's a different query entirely" statement. Made a mimstake. However, as I mentioned above, when I look at the query in Query Builder, it "looks" like a table and has fields. Is this a "virtual" table? Sorry for my ignorance here. In query builder a query and a table look the same. A query has fields with field-names just like a table so I'm not sure why you think they would not be represented in the same way. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#8
|
|||
|
|||
Hank, what you've described is that it sounds like you have a combobox and you opened the query
builder from design view. What you are looking at now is an SQL Statement that is being used for the rowsource of your combobox. If you look through your queries this sql statement will not be shown in the list of queries in the database window because it is stored behind the scenes. You could click Save while in the query builder, give it a name then it will be stored as a query in your application. The other part of your concern deals with a table and a query being shown joined in the SQL Statement:Query Builder. You can base a query on one or more tables, one or more queries, or a combination of tables and saved queries etc.. So what you're seeing in the query builder is the result of the original designer opening the Query Builder, Clicking View/Show Tables from the main menu, then selecting the table "tbldid800holdings" from the list and the query "qryuseddidholdings" from the list. They then joined the table and query on a common field which in your case is "tbldid800holdings.did = qryuseddidholdings.did ". Hope this helps! -- Reggie ---------- "Rick Brandt" wrote in message ... "Hank" wrote in message ... LeAnne wrote: it's querying (Whew, that was a mouthful!). Or am I misunderstanding your point? It might help if you clarified what you mean by "It's a different query entirely." Ignore my "it's a different query entirely" statement. Made a mimstake. However, as I mentioned above, when I look at the query in Query Builder, it "looks" like a table and has fields. Is this a "virtual" table? Sorry for my ignorance here. In query builder a query and a table look the same. A query has fields with field-names just like a table so I'm not sure why you think they would not be represented in the same way. -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#9
|
|||
|
|||
Reggie wrote:
Hank, what you've described is that it sounds like you have a combobox and you opened the query builder from design view. What you are looking at now is an SQL Statement that is being used for the rowsource of your combobox. If you look through your queries this sql statement will not be shown in the list of queries in the database window because it is stored behind the scenes. You could click Save while in the query builder, give it a name then it will be stored as a query in your application. The other part of your concern deals with a table and a query being shown joined in the SQL Statement:Query Builder. You can base a query on one or more tables, one or more queries, or a combination of tables and saved queries etc.. So what you're seeing in the query builder is the result of the original designer opening the Query Builder, Clicking View/Show Tables from the main menu, then selecting the table "tbldid800holdings" from the list and the query "qryuseddidholdings" from the list. They then joined the table and query on a common field which in your case is "tbldid800holdings.did = qryuseddidholdings.did ". Hope this helps! Reggie, Thanks for the explanation! What a learning exercise this was. Thanks again. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
Row source of table referring to same table? | Noozer | Running & Setting Up Queries | 2 | August 12th, 2004 08:29 PM |
Table Error Message | Di | New Users | 2 | June 30th, 2004 07:57 AM |
Name not showing ID is | René | Setting Up & Running Reports | 11 | June 29th, 2004 01:40 AM |
Image Control Table | John Gavin | General Discussion | 3 | June 28th, 2004 04:21 AM |