When is a Table not a Table?

  • Thread starter Thread starter Hank
  • Start date Start date
H

Hank

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
 
Hank said:
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?
Click to expand...

Yes, you're missing the fact that a query can use another query as an
input, not just tables.
 
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.
 
Rick said:
Yes, you're missing the fact that a query can use another query as an
input, not just tables.
Click to expand...

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.
 
Hank said:
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?
Click to expand...

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
 
LeAnne said:
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."
Click to expand...

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.
 
Hank said:
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.
Click to expand...

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.
 
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 said:
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!
Click to expand...


Reggie,

Thanks for the explanation! What a learning exercise this was.

Thanks again.
 
Back
Top