A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

minimize repeating query criteria & generating monthly YTD totals



 
 
Thread Tools Display Modes
  #1  
Old May 11th, 2010, 06:07 PM posted to microsoft.public.access.queries
Liv[_2_]
external usenet poster
 
Posts: 21
Default 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  
Old May 11th, 2010, 06:30 PM posted to microsoft.public.access.queries
Liv[_2_]
external usenet poster
 
Posts: 21
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.