UNION QUERY

[geebee]

hi,

I have the following query:
SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[COUNT( * )]) AS
[SumOfCOUNT( * )],
"line1" as line,
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt
ORDER by
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt
UNION ALL
SELECT
tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
Sum(tbl_CACS_activity_by_resolvedstatusandcontactcode.[CountOfLoan Acct #])
AS [SumOfCountOfLoan Acct #],
"line2" as line,
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt
FROM tbl_CACS_activity_by_resolvedstatusandcontactcode
GROUP BY tbl_CACS_activity_by_resolvedstatusandcontactcode.ResolvedStatus,
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt
ORDER BY
tbl_CACS_activity_by_resolvedstatusandcontactcode.Resolveddt;

here is some sample data:
ResolvedStatus SumOfCOUNT( * ) line Resolveddt
BK_PAYOFF 2 line1 6/3/2006
CURR_PD 18529 line2 6/3/2006
PAYOFF 271 line2 6/3/2006
CURR_AMD/DFR 43 line2 6/3/2006
BK_PAYOFF 4 line2 6/3/2006
BK_CHGOFF 10 line2 6/3/2006
BK_CHGOFF 2 line1 6/3/2006
PAYOFF 20 line1 6/3/2006
CURR_AMD/DFR 17 line1 6/3/2006
CURR_PD 120 line1 6/3/2006
CURR_PD 33594 line2 6/5/2006
BK_CHGOFF 2 line1 6/5/2006
BK_REPO 3 line1 6/5/2006
PAYOFF 641 line2 6/5/2006
CURR_AMD/DFR 1881 line2 6/5/2006
CHGOFF 9 line1 6/5/2006
CHGOFF 70 line2 6/5/2006
BK_REPO 17 line2 6/5/2006
BK_PAYOFF 21 line2 6/5/2006
REPO 63 line1 6/5/2006
CURR_AMD/DFR 126 line1 6/5/2006
CURR_PD 196 line1 6/5/2006

GOAL: to get the preceding data into one graph comparing compares the number
of resolved accounts by resolved status and resolveddt vs. the number of CACS
calls by resolved status and resolveddt. Each resolvedstatus should have 2
lines. For example, CURR_PD category should have a line for number of
resolved over resolveddt timeline, AND another line for number of calls over
resolveddt timeline.

HOW???

Thanks in advance,
geebee

 

[[MVP] S.Clark]

Trying to do this in one shot is probably not going to cut it. And if it
does, then it will be very difficult to maintain. When all else fails,
create a table of which the graph/report will be based.

Use action queries(multiple if needed) to populate the table as needed.

--
Steve Clark, Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
Professional Access Database Repair
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html