Union Query by Month

  • Thread starter Thread starter Liv
  • Start date Start date
L

Liv

I am current using a Union Query like this:

SELECT Region, Count([Region]) AS Count, Sum([Total Value]) AS Sum,
Avg([DateApproved]-[DateReceived]) AS [Avg], "A" AS MyTable
FROM [tblA]
GROUP BY Region
UNION ALL SELECT Region, Count([Date]) AS Count, Sum([DollarAmount]) AS Sum,
Avg([AnotherDate]-[Date]) AS [Avg], "B1" AS MyTable
FROM [tblB]
GROUP BY Region
UNION ALL SELECT Region, Count([AnotherDate]) AS Count, Sum([DollarAmount])
AS Sum, Avg([OtherDate]-[AnotherDate]) AS [Avg], "B2" AS MyTable
FROM [tblB]
GROUP BY Region;

Which returns something like this:

Region Count Sum Avg MyTable
NCAL 16 552 3.75 A
SCAL 28 915 A
NCAL 322 5555 1.39 B1
SCAL 378 516 31 B1
NCAL 2 944 B2
SCAL 130 7457 1.38 B2

I would like to break this info out by date returning something like this:

Region YTDCount... Jan2009Count... Dec2009Sum YTDAvgMyTable
NCAL 16 3 6107 A
SCAL 28 2 11000 A
NCAL 322 29 5547 B1
SCAL 378 28 62009 B1
NCAL 2 0 B2
SCAL 130 7 5803 B2
 
Your union query has 5 dates - [DateApproved], [DateReceived], [AnotherDate],
[Date], and [OtherDate]. So which one of then do you want to use in the
output for Jan2009Count... and Dec2009Sum?
 
[DateReceived], [Date], and [AnotherDate]. I indicated so many different
fields because each table/query has the dates named differently, just as the
Count function calls on different fields according to the data in each table.
Is it possible?

KARL DEWEY said:
Your union query has 5 dates - [DateApproved], [DateReceived], [AnotherDate],
[Date], and [OtherDate]. So which one of then do you want to use in the
output for Jan2009Count... and Dec2009Sum?

Liv said:
I am current using a Union Query like this:

SELECT Region, Count([Region]) AS Count, Sum([Total Value]) AS Sum,
Avg([DateApproved]-[DateReceived]) AS [Avg], "A" AS MyTable
FROM [tblA]
GROUP BY Region
UNION ALL SELECT Region, Count([Date]) AS Count, Sum([DollarAmount]) AS Sum,
Avg([AnotherDate]-[Date]) AS [Avg], "B1" AS MyTable
FROM [tblB]
GROUP BY Region
UNION ALL SELECT Region, Count([AnotherDate]) AS Count, Sum([DollarAmount])
AS Sum, Avg([OtherDate]-[AnotherDate]) AS [Avg], "B2" AS MyTable
FROM [tblB]
GROUP BY Region;

Which returns something like this:

Region Count Sum Avg MyTable
NCAL 16 552 3.75 A
SCAL 28 915 A
NCAL 322 5555 1.39 B1
SCAL 378 516 31 B1
NCAL 2 944 B2
SCAL 130 7457 1.38 B2

I would like to break this info out by date returning something like this:

Region YTDCount... Jan2009Count... Dec2009Sum YTDAvgMyTable
NCAL 16 3 6107 A
SCAL 28 2 11000 A
NCAL 322 29 5547 B1
SCAL 378 28 62009 B1
NCAL 2 0 B2
SCAL 130 7 5803 B2
Click to expand...
Click to expand...
 
Your union query outputs no date field. You got to have a date so you can
total by month.

Liv said:
[DateReceived], [Date], and [AnotherDate]. I indicated so many different
fields because each table/query has the dates named differently, just as the
Count function calls on different fields according to the data in each table.
Is it possible?

KARL DEWEY said:
Your union query has 5 dates - [DateApproved], [DateReceived], [AnotherDate],
[Date], and [OtherDate]. So which one of then do you want to use in the
output for Jan2009Count... and Dec2009Sum?

Liv said:
I am current using a Union Query like this:

SELECT Region, Count([Region]) AS Count, Sum([Total Value]) AS Sum,
Avg([DateApproved]-[DateReceived]) AS [Avg], "A" AS MyTable
FROM [tblA]
GROUP BY Region
UNION ALL SELECT Region, Count([Date]) AS Count, Sum([DollarAmount]) AS Sum,
Avg([AnotherDate]-[Date]) AS [Avg], "B1" AS MyTable
FROM [tblB]
GROUP BY Region
UNION ALL SELECT Region, Count([AnotherDate]) AS Count, Sum([DollarAmount])
AS Sum, Avg([OtherDate]-[AnotherDate]) AS [Avg], "B2" AS MyTable
FROM [tblB]
GROUP BY Region;

Which returns something like this:

Region Count Sum Avg MyTable
NCAL 16 552 3.75 A
SCAL 28 915 A
NCAL 322 5555 1.39 B1
SCAL 378 516 31 B1
NCAL 2 944 B2
SCAL 130 7457 1.38 B2

I would like to break this info out by date returning something like this:

Region YTDCount... Jan2009Count... Dec2009Sum YTDAvgMyTable
NCAL 16 3 6107 A
SCAL 28 2 11000 A
NCAL 322 29 5547 B1
SCAL 378 28 62009 B1
NCAL 2 0 B2
SCAL 130 7 5803 B2
Click to expand...
Click to expand...
Click to expand...
 
Back
Top