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
|
|||
|
|||
minimize repeating query criteria & generating monthly YTD totals
I have 5 queries. One for each month. There are # things I want to do here.
Consolidate to a single query. currently the "Query is too complex" message appears. Simplify my code. I'm only changing the date range in my criteria, but repeating the Aggregate a YTD column for Count, Sum, Avg Completion Time fields within the same record. Please keep in mind that my query must start with December 2009 to current. "Location" Mandatory field for all tables SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS [Avg Completion Time], "Table_A April 2010" AS MyTable FROM [Table_A] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query April 2010" AS MyTable FROM [Table_B 50k or More Query] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less than 100k Query April 2010" AS MyTable FROM [Table_B Less than 50k] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April 2010" AS MyTable FROM [Table_C] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Region .. .. .. UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS [Avg Completion Time], "Table_A April YTD 2010" AS MyTable FROM [Table_A] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query April YTD 2010" AS MyTable FROM [Table_B 50k or More Query] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less than 100k Query April YTD 2010" AS MyTable FROM [Table_B Less than 50k] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April YTD 2010" AS MyTable FROM [Table_C] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Region; |
#2
|
|||
|
|||
minimize repeating query criteria & generating monthly YTD totals
I slipped up a bit in my labeling. The 100k should be 50k.
"Liv" wrote: I have 5 queries. One for each month. There are # things I want to do here. Consolidate to a single query. currently the "Query is too complex" message appears. Simplify my code. I'm only changing the date range in my criteria, but repeating the Aggregate a YTD column for Count, Sum, Avg Completion Time fields within the same record. Please keep in mind that my query must start with December 2009 to current. "Location" Mandatory field for all tables SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS [Avg Completion Time], "Table_A April 2010" AS MyTable FROM [Table_A] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query April 2010" AS MyTable FROM [Table_B 50k or More Query] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less than 100k Query April 2010" AS MyTable FROM [Table_B Less than 50k] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April 2010" AS MyTable FROM [Table_C] WHERE [Date Received] BETWEEN #4/1/2010# AND #4/30/2010# GROUP BY Region . . . UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Amount]) AS Sum, "" AS [Avg Completion Time], "Table_A April YTD 2010" AS MyTable FROM [Table_A] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Date_2]-[Date_1]) AS [Avg Completion Time], "Table_B 100k or More Query April YTD 2010" AS MyTable FROM [Table_B 50k or More Query] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Location, Count([Location]) AS Count, Sum([Value]) AS Sum, Avg([Approval Date]-[Date Received]) AS [Avg Completion Time], "Table_B Less than 100k Query April YTD 2010" AS MyTable FROM [Table_B Less than 50k] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Location UNION ALL SELECT Region, Count([Location]) AS Count, Sum([Total Value]) AS Sum, Avg([Date B]-[Date A]) AS [Avg Completion Time], "Table_C April YTD 2010" AS MyTable FROM [Table_C] WHERE [Date Received] BETWEEN #1/1/2010# AND #4/30/2010# GROUP BY Region; |
Thread Tools | |
Display Modes | |
|
|