Nested Queries

[davies107]

hello everyone,

just want to ask what are NESTED QUERIES?

i have a database that involves lots of fields in the query. before, it has only 32 fields in the query but now it has 43. by the time it went to 43 fields, some records arent showing up in the listbox. maybe i go beyond the limitation on the number of fields in the query? as i checked MS-Access specification, number of fields in a recordset is 255, number of tables in a query is 32, and number of levels of NESTED QUERIES is 50. i dont get what are NESTED QUERIES.

sooner or later, the fields in my query will go higher (more than 43) but wont go beyond 255.

did i make a mistake here? why some items or records are not showing up in the listbox? i did not touch anything only that number of fields in queries are increasing.

any help/enlighten is highly appreciated.

Thanks!

 

[plog]

A nested query is a query that uses another query as a datasource. Suppose you have a table that lists all employees of a company. You could build a query off that table to show just the people in Accounting. Then you could create a new query using that Accounting query to sum up all the salaries of people in Accounting. That's a nested query.

Why will your fields go higher? My guess is because you have fields named after dates (i.e. BalanceOctober, BalanceNovember) or something that requires you to add new fields as time passes. Could you show the fields of the table this query is based on as well as the fields of the query itself?

 

[Brianwarnock]

I've always known these as cascading queries , I would have thought that sub queries were more likely to be considered nested, but I am shooting in the dark with no formal education I don't know the correct terminology always , and I could be misreading what plog is saying as in both cases one query uses another queries results as its data source.


Brian

 

[plog]

but I am shooting in the dark with no formal education I don't know the correct terminology always

I'm right there with you-- I always love these guys who come onto these boards spouting "4NF" and "ERM" and other jargon that I am not familiar with, but then post questions containing simple normalization errors that they have made. This time I got off my lazy butt and googled 'Cascading Query' and 'Nested Query' to make sure I wasn't talking like an idiot in my first post.

From what I found, I don't believe 'Cascading Query' is an actual database term. For 'Nested' query I found that it essentially means a sub-query. My way of doing it (build a query then use it as the source in another query) is technically correct. But a more formal nested query actually contains a query statement within a query statement as such:

[FONT=Courier New]SELECT id, first_name [/FONT]
[FONT=Courier New]FROM student_details [/FONT]
[FONT=Courier New]WHERE first_name IN (SELECT first_name [/FONT]
[FONT=Courier New]FROM student_details [/FONT]
[FONT=Courier New]WHERE subject= 'Science'); [/FONT]

 

[Brianwarnock]

I've come across the term cascading queries on this forum, it makes sense in English as running one of the queries causes other queries to run.
A discussion on here several years ago, which ican not find, between a few MVPs discussed the merits of each approach, and the view was that the system was more likely to optimise the cascading queries over the sub queries.

There are however instances where a sub query is the only approach.

Brian

 

[Brianwarnock]

http://access.mvps.org/access/queries/qry0020.htm

The above site uses the term cascading query.

Brian

 

[davies107]

yes that is correct - my fields will go higher or will keep increasing as time passes because i still have records left. i stopped encoding/putting it when i realized that my listbox number of records decreases.


attached are snapshots of my database:

1st: Query ;
2nd: Number of records decreases (it should be 4743 or more not 573 ; 3rd: Table ;
4th: Table in Design View