subquery question

[n8kindt]

so i've read up a little bit about subqueries. honestly, i do not see the point. from what i get out of it, a query with a subquery is very similar (if not identical?) to a query using another query as its data source. am i right on this one?

thank you for your replies.
nate

[Delerna]

Yes you are perfectly correct, A query that has a subquery is exactly like a query joined to another query.

The attraction of subqueries, for me anyway, is that you finish up with less queries in the list queries tab. When a query is being written solely to massage date for use by another query and the query that is doing the massaging will never be used by anything else, then it makes sense to write the massaging query as a subquery within the main query. That way I finish up with 1 query instead of 2 or 3 . It may seem a minor point but a large project can have hundreds of queries many of which performing data massaging for single queries.

Using subqueries eliminates those which makes it easier for me to come back to the project 1 year after it was finished and still be able to find my way around.

By the way, for the very same reason stated above I do the following
If a form needs to run an update or a delete query I use DAO or ADO to execute a query string in the VBA code section of the form rather than have query in the querydefs. In the forms code the purpose of the query is more obvious than if it was in the queries tab and there is 1 or more less queries hiding amongst hundreds.

Anyway, those are my reasons.

[n8kindt]

thanks, Delerna. yeah, i kind of figured that was the case. i can see how it would be less messy with fewer outwardly visible queries. it would guess that there is no performance-related penalties for using either option. i was just wondering this b/c i have a database that is in its final stages and i was considering stripping it down and switching from a smorgasboard of queries to subqueries. i don't think the end justifies the means in this case. instead, i will just take extra time in organizing the sidebar listings into clear categories.

[FishVal]

There is an article in HowTos section describing various ways subqueries may be used.

[n8kindt]

There is an article in HowTos section describing various ways subqueries may be used.

fish, i have no idea how your replies seem to fly under my radar but i just noticed THIS reply too! as always, thank you for your reply. anyways, i found something interesting after downloading the sample file in the howto section u pointed me to--it got to the root of my "query" (ha). u may already know this but...

i opened the sample db on my computer at home (which uses 2003) and realized i couldn't look at the subqueries in design view. the computer i use at work is access 2007. i created this thread b/c when i made a subquery in 2007, i still was able to view the query in design view (with the exception of the subquery field itself--it was displayed in SQL language as an expression). so u can imagine why i wondered why subqueries where such a big deal. in design view, it looks nearly identical to a query using another query field as a reference.

if u guys have no idea what i'm talking about and are interested, i will post a screenshot of what i'm talking about when i get back to work tomorrow.

[FishVal]

Hi, Nathan.

I use Access 2003 and in design view it represents subquery in FROM clause like table/query even if subquery itself could not be represented in design view (e.g. joins not based on fields equality). This makes evidence that Access just looks for available fields in subquery.

Anyway, I would not worry about it as soon as it could be executed, no matter the query builder is unable to bring a picture. Not an outstanding case. ;)

Regards,
Fish