Subtracting select queries form each other (each select query has multiple conditions)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Work.Smarter.1@gmail.com - 25 Feb 2006 22:35 GMT

How do i subtract the first value from the second value without
multiple queries? (query example below)

SELECT Sum(tbl_Area_PropLend.Amount) AS Default_FinCharge
FROM tbl_Area_PropLend
GROUP BY tbl_Area_PropLend.ReportingMonth, tbl_Area_PropLend.Measure,
tbl_Area_PropLend.Type
HAVING (((tbl_Area_PropLend.ReportingMonth)=#8/1/2004#) AND
((tbl_Area_PropLend.Measure)=1) AND ((tbl_Area_PropLend.Type)=2));

SELECT Sum(tbl_Area_PropLend.Amount) AS DefaultADB
FROM tbl_Area_PropLend
GROUP BY tbl_Area_PropLend.ReportingMonth, tbl_Area_PropLend.Measure,
tbl_Area_PropLend.Type
HAVING (((tbl_Area_PropLend.ReportingMonth)=#8/1/2004#) AND
((tbl_Area_PropLend.Measure)=2) AND ((tbl_Area_PropLend.Type)=2));

Reply to this Message

MGFoster - 26 Feb 2006 21:02 GMT

Like this:

SELECT Sum(IIf([Measure]=1,Amount,0)) AS Default_FinCharge,
Sum(IIf([Measure]=2,Amount,0)) As DefaultADB,
Sum(IIf([Measure]=2,Amount,0))-Sum(IIf([Measure]=1,Amount,0)) As
Difference
FROM tbl_Area_PropLend
WHERE ReportingMonth=#8/1/2004#
AND [Measure] IN (1,2)
AND [Type]=2
GROUP BY ReportingMonth, [Measure], [Type]

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> How do i subtract the first value from the second value without
> multiple queries? (query example below)
[quoted text clipped - 12 lines]
> HAVING (((tbl_Area_PropLend.ReportingMonth)=#8/1/2004#) AND
> ((tbl_Area_PropLend.Measure)=2) AND ((tbl_Area_PropLend.Type)=2));

Reply to this Message