minimize repeating query criteria & generating monthly YTD totals

[Liv]

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;

 

[Liv]

I slipped up a bit in my labeling. The 100k should be 50k.