Is my query too complex?

[Roses'' HelpDesk]

I have an Access 2k database that successfully compares Delivery Charge data
for 2006 & 2007. I recently incorporated data for 2008 also. Two of my
queries won't run once the third year of data is added. Is my query too
complex?
This query shows the following data:
Customer Code (tblCustomerData) - Group By
Customer Name (tblCustomerData) - Group By
Sales Assoc Name (tblSalesAssoc) - Group By
Customer Class (tblCustomerData) - Group By
Group ID (tblCustomerData) - Group By
Customer Division (tblCustomerData) - Group By - Criteria: "Retail"
08InvoiceAmount (qryNetInvAmt2008) - Sum
07InvoiceAmount (qryNetInvAmt2007) - Sum
06InvoiceAmount (qryNetInvAmt2006) - Sum
08SundayDeliveries (qry2008Sunday) - Sum
08MondayDeliveries (qry2008Monday) - Sum
08TuesdayDeliveries (qry2008Tuesday) - Sum
08WednesdayDeliveries (qry2008Wednesday) - Sum
08ThursdayDeliveries (qry2008Thursday) - Sum
08FridayDeliveries (qry2008Friday) - Sum
08SaturdayDeliveries (qry2008Saturday) - Sum
07SundayDeliveries (qry2007Sunday) - Sum
07MondayDeliveries (qry2007Monday) - Sum
07TuesdayDeliveries (qry2007Tuesday) - Sum
07WednesdayDeliveries (qry2007Wednesday) - Sum
07ThursdayDeliveries (qry2007Thursday) - Sum
07FridayDeliveries (qry2007Friday) - Sum
07SaturdayDeliveries (qry2007Saturday) - Sum
06SundayDeliveries (qry2006Sunday) - Sum
06MondayDeliveries (qry2006Monday) - Sum
06TuesdayDeliveries (qry2006Tuesday) - Sum
06WednesdayDeliveries (qry2006Wednesday) - Sum
06ThursdayDeliveries (qry2006Thursday) - Sum
06FridayDeliveries (qry2006Friday) - Sum
06SaturdayDeliveries (qry2006Satday) - Sum

In total this combines data from 2 tables & 27 queries.
I have another similar one that's not working with 2 tables & 21 queries.
If I break this down to have the 2008 data by itself everything works which
leads me to believe that there's nothing wrong with the queries themselves.
My next largest query (that's working) has 2 tables & 18 queries.

 

[John W. Vinson]

I have an Access 2k database that successfully compares Delivery Charge data
for 2006 & 2007. I recently incorporated data for 2008 also. Two of my
queries won't run once the third year of data is added. Is my query too
complex?
This query shows the following data:
Customer Code (tblCustomerData) - Group By
Customer Name (tblCustomerData) - Group By
Sales Assoc Name (tblSalesAssoc) - Group By
Customer Class (tblCustomerData) - Group By
Group ID (tblCustomerData) - Group By
Customer Division (tblCustomerData) - Group By - Criteria: "Retail"
08InvoiceAmount (qryNetInvAmt2008) - Sum
07InvoiceAmount (qryNetInvAmt2007) - Sum
06InvoiceAmount (qryNetInvAmt2006) - Sum
08SundayDeliveries (qry2008Sunday) - Sum
08MondayDeliveries (qry2008Monday) - Sum
08TuesdayDeliveries (qry2008Tuesday) - Sum
08WednesdayDeliveries (qry2008Wednesday) - Sum
08ThursdayDeliveries (qry2008Thursday) - Sum
08FridayDeliveries (qry2008Friday) - Sum
08SaturdayDeliveries (qry2008Saturday) - Sum
07SundayDeliveries (qry2007Sunday) - Sum
07MondayDeliveries (qry2007Monday) - Sum
07TuesdayDeliveries (qry2007Tuesday) - Sum
07WednesdayDeliveries (qry2007Wednesday) - Sum
07ThursdayDeliveries (qry2007Thursday) - Sum
07FridayDeliveries (qry2007Friday) - Sum
07SaturdayDeliveries (qry2007Saturday) - Sum
06SundayDeliveries (qry2006Sunday) - Sum
06MondayDeliveries (qry2006Monday) - Sum
06TuesdayDeliveries (qry2006Tuesday) - Sum
06WednesdayDeliveries (qry2006Wednesday) - Sum
06ThursdayDeliveries (qry2006Thursday) - Sum
06FridayDeliveries (qry2006Friday) - Sum
06SaturdayDeliveries (qry2006Satday) - Sum

In total this combines data from 2 tables & 27 queries.
I have another similar one that's not working with 2 tables & 21 queries.
If I break this down to have the 2008 data by itself everything works which
leads me to believe that there's nothing wrong with the queries themselves.
My next largest query (that's working) has 2 tables & 18 queries.

Since you could probably get all this information with one single Crosstab
query with no subqueries, I'd say yes, your query IS too complex. What are all
these qry2007Friday etc. queries? How are they related to the main query?


John W. Vinson [MVP]

 

[Roses'' HelpDesk]

Each of the days includes Criteria that narrows the data to that day. It also
narrows the criteria in about three other ways (invoices only & not credit
memos, just items that have delivery routs, etc.). It also creates a count of
each of the unique entries so that I have a count of the number of deliveries
on each day.

I tried a few more things yesterday & got it working. I wound up putting
each year in its own query & then combining the three so that the final query
only had three to combine. I guess Access 2000 just can't handle more than 20
queries in one.