Query vs. Subquery 1

[zimmer9]

I run the following query named Query5 and get the results that follow:

SELECT C.CustomerNumber, C.DateLost, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS TOTAL, "LT6" AS AGE
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";

Cust# DateLost TOTAL AGE
------ ---------- --------- ----
023414 10/01/2004 4981.44 LT6
036983 12/01/2004 3098.94 LT6
047370 11/01/2004 598.76 LT6
062992 11/01/2004 43.21 LT6
066173 11/01/2004 17570.00 LT6
079580 11/01/2004 16173.51 LT6
138967 08/01/2004 29581.57 LT6

AGE of LT6 means less than 6 months old.
I would like the query to return another field which I will call DollarRange and would have the following values:

DollarValue Total
------------------ ------------------------------
LR (for low range) between 0 and 1,000.00
MR (for mid range) between 1,000.01 and 15,000.00
HR (for high range) over 15,000.00

Thus, my goal is to achieve the following results:

Cust# DateLost TOTAL AGE DOLLARVALUE
------ ---------- --------- ---- -----------
023414 10/01/2004 4981.44 LT6 MR
036983 12/01/2004 3098.94 LT6 MR
047370 11/01/2004 598.76 LT6 LR
062992 11/01/2004 43.21 LT6 LR
066173 11/01/2004 17570.00 LT6 MR
079580 11/01/2004 16173.51 LT6 HR
138967 08/01/2004 29581.57 LT6 HR

Do you think a subquery is possible which would be more efficient or should I run 1 query to get the result set I listed above (Query5) and then a separate query to get the additional column titled DOLLARVALUE (Query6).

I ran the following query (Query6) after running the query (Query5) I listed above and it works fine.

SELECT C.CustomerNumber, DateLost, Total, AGE, 'LR'
FROM Query5
where Total between 0 and 1000.00;

Union ALL SELECT C.CustomerNumber, DateLost, Total, AGE, 'MR'
FROM Query5
where Total between 1000.01 and 15000.00;

UNION ALL SELECT C.CustomerNumber, DateLost, Total, AGE, 'HR'
FROM Query5
where Total > 15000.00;

CustNum DateLost Total AGE DOLLARVALUE
047370 11/1/2004 598.76 LT6 LR
062992 11/1/2004 43.21 LT6 LR
023414 10/1/2004 4981.44 LT6 MR
036983 12/1/2004 3098.94 LT6 MR
066173 11/1/2004 17570.00 LT6 HR
079580 11/1/2004 16173.51 LT6 HR
138967 8/1/2004 29581.57 LT6 HR

 

[traingamer]

One way:

SELECT C.CustomerNumber, C.DateLost, Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0)) AS TOTAL,
 "LT6" AS AGE, 
iif(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))>15000,"HR",
iif(Sum(Nz(P.MarketValue,0)+Nz(P.CashBalance,0))>1000, "MR", "LR"))
...

traingamer