SQL UNION 1

[zimmer9]

I created 5 SELECT UNION statements as follows. I created 1 aggregate record per Customer Number to get their total MarketValue/Cash value (the sum of 2 separate fields). The reason I used a Union was because ultimately I will be creating an Aging report that breaks down into different time periods as follows:

for ex:
Less than 6 months
Customers With 100K+ Assets
With $50K - 100K Assets
With $25 - 50K Assets
With $10K - $25K Assets
With $1K - 10K Assets
Under 1K Assets

6 months to 1 year (with the same Asset ranges)

1 year to 2 years (with the same Asset ranges)

2 years to 3 years (with the same Asset ranges)

3 plus years (with the same Asset ranges)


The 1st SQL Statements is as follows:

SELECT C.CustomerNumber, C.DateLost, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))<1000,"L1") AS CAT1, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 1000 And 9999.99,"L2") AS CAT2, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 10000 And 24999.99,"L3") AS CAT3, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 25000 And 49999.99,"L4") AS CAT4, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) Between 50000 And 99999.99,"L5") AS CAT5, IIf(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))>100000,"L6") AS CAT6, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS Total, "LT6" AS LT6
FROM tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber=P.CustomerNumber
WHERE (((C.DateLost) Between #8/1/2004# And #1/31/2005#))
GROUP BY C.CustomerNumber, C.DateLost, "LT6";

This query compares against a DateLost field on the data base records and creates the first set of records for the period of time: the last 6 months which is the first part of my report.

Then 1 basically created 4 similar SQL Union statements for the other 4 time periods.

The 5 SELECT unions generated the following record format:

CustNum DateLost CAT1 CAT2 CAT3
090085 7/21/1993 L26

----------------------------------------------


CAT4 CAT5 CAT6 Total
5352.36

Because I used the union statement in the CAT1 field,
I get a value of L1, L7, L13, L20, L27 in this field. In other words, there are 6 different dollar ranges as well as 5 different time frames for my report. Thus there are 30 different dollar buckets in which to add the dollar value for each record (6 dollar ranges times 5 different Aging time periods). I used the values L1 though L 6 for the first time frame, L7 though L12 for the 2nd time frame ...
L25 through L30 for the last time frame.

Because I used a Union statement and all fields have to line up 1 under the other, L1 is in the same column as L7, L13, L20 and L26.

Thus the L value signified which dollar range and time frame to place the dollar total into the Report fields.
The report line is based on:
a)the Age of the record (Date Lost, how old)
b)the dollar value within the different dollar ranges of Asset classes (under 1K, 1K to 10K).

In other words values of L1 throught L6 are for the first part of the report for the time frame Less Than 6 Months

L6 = With 100K+ Assets
L5 = With $50K - 100K Assets
L4 = With $25 - 50K Assets
L3 = With $10K - $25K Assets
L2 = With $1K - 10K Assets
L1 = Under 1K Assets


6 months to 1 year
L12 = Customers With 100K+ Assets
L11 = With $50K - 100K Assets
L10 = With $25 - 50K Assets
L9 = With $10K - $25K Assets
L8 = With $1K - 10K Assets
L7 = Under 1K Assets

I end up with records like the following:

Cust# CAT1 CAT2 CAT3 CAT4 CAT5 CAT6 TOTAL
----- ---- ---- ---- ---- ---- ---- -----
12345 L3 15000
23455 L2 8000
34456 L1 500
45433 L1 400
56434 L8 9000
67654 L9 12000
77777 L7 400
83335 L5 10000
99999 L7 200

Is there a way to total these records if they have different values in a field ?

In this example L1 should have 900 dollars (2 records with values of 500 and 400. L7 is in the same column as L1 and should have a value of 600 dollars, the sum of 400 plus 200.

Or is there a better way to calculate the values ?

 

[PHV]

Say the query returning
Cust# CAT1 CAT2 CAT3 CAT4 CAT5 CAT6 TOTAL
is named qryCustCatTotal.
You want something like this ?
SELECT CAT1 AS Cat, Sum(TOTAL) AS TotalCat FROM qryCustCatTotal WHERE CAT1 Is Not Null GROUP BY CAT1
UNION SELECT CAT2, Sum(TOTAL) FROM qryCustCatTotal WHERE CAT2 Is Not Null GROUP BY CAT2
...
UNION SELECT CAT6, Sum(TOTAL) FROM qryCustCatTotal WHERE CAT6 Is Not Null GROUP BY CAT6;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[zimmer9]

PHV, you cease to amaze me. Thanks again. You answered my somewhat convoluted question with clarity.