asked on QUERY WITH SUM
If have the following Query for computing sum of hotel guests etc;
SELECT DISTINCTROW QueryFull.AGENCY, Sum(QueryFull.RESVALUE) AS SumResvalue, First(QueryFull.CANCEL) AS [First Of CANCEL]
FROM QueryFull
WHERE (((QueryFull.CANCEL)=False
GROUP BY QueryFull.AGENCY
ORDER BY Sum(QueryFull.RESVALUE) DESC;
I now would like to change this query to not only include the SUM of RESVALUE but also the sum of RESVALUE but only if PCKGID=1. Is this possible in the same query?
SELECT DISTINCTROW QueryFull.AGENCY, Sum(QueryFull.RESVALUE) AS SumResvalue, First(QueryFull.CANCEL) AS [First Of CANCEL]
FROM QueryFull
WHERE (((QueryFull.CANCEL)=False
GROUP BY QueryFull.AGENCY
ORDER BY Sum(QueryFull.RESVALUE) DESC;
I now would like to change this query to not only include the SUM of RESVALUE but also the sum of RESVALUE but only if PCKGID=1. Is this possible in the same query?
Microsoft Access
Last Comment
Michael Paravicini
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
> "but also the sum of RESVALUE but only if PCKGID=1"
I took this to mean that all the records would still be there as before, just that some of them are summed -- so slightly modifying what Rey gave you if this is the case:
, First(QueryFull.CANCEL) AS [First Of CANCEL]
is irrelevant since false records are knocked out by criteria
if changes need to be made to QueryFull, we will need to see that SQL, thanks
I took this to mean that all the records would still be there as before, just that some of them are summed -- so slightly modifying what Rey gave you if this is the case:
SELECT DISTINCTROW QueryFull.AGENCY
, Sum( iif(QueryFull.PCKGID=1,QueryFull.RESVALUE,0) ) AS SumResvalue
FROM QueryFull
WHERE QueryFull.CANCEL=False
GROUP BY QueryFull.AGENCY
ORDER BY Sum( iif(QueryFull.PCKGID=1,QueryFull.RESVALUE,0) ) DESC;
, First(QueryFull.CANCEL) AS [First Of CANCEL]
is irrelevant since false records are knocked out by criteria
if changes need to be made to QueryFull, we will need to see that SQL, thanks
ASKER
Thank you so much!
FROM QueryFull
WHERE QueryFull.CANCEL=False AND QueryFull.PCKGID=1
GROUP BY QueryFull.AGENCY
ORDER BY Sum(QueryFull.RESVALUE) DESC;