Query to Subtract two queries

[ambrn14]

I have two queries and give me a result for each that I want to subtract. In the SQL world, this is not as easy as it sounds. I am about the drive myself insane. And the info. that I have found on the internet has been no help.

Here are the two queries-
TotalEDCalled=
SELECT Month([CodeStrokeDate]) & "/" & Year([CodeStrokeDate]) AS [Month/Year], Count(tblCodeStrokePts.CodeStrokeDate) AS [Total ED Called]
FROM tblCodeStrokePts
WHERE (((tblCodeStrokePts.CodeStrokeDate) Between [Forms]![frmQuerySwitchboard]![txtStartdate] And [Forms]![frmQuerySwitchboard]![txtEnddate]))
GROUP BY Month([CodeStrokeDate]) & "/" & Year([CodeStrokeDate]), Year([CodeStrokeDate]), Month([CodeStrokeDate]), tblCodeStrokePts.InHouseCS, tblCodeStrokePts.IVtPADripAndShip
HAVING (((tblCodeStrokePts.InHouseCS)="No" Or (tblCodeStrokePts.InHouseCS) Is Null Or (tblCodeStrokePts.InHouseCS)="N") AND ((tblCodeStrokePts.IVtPADripAndShip)="No" Or (tblCodeStrokePts.IVtPADripAndShip) Is Null Or (tblCodeStrokePts.IVtPADripAndShip)="N"))
ORDER BY Year([CodeStrokeDate]), Month([CodeStrokeDate]);

And

TotalEDCanceled=
SELECT Month([CodeStrokeDate]) & "/" & Year([CodeStrokeDate]) AS [Month/Year], Count(tblCodeStrokePts.CodeStrokeDate) AS [Total ED Canceled]
FROM tblCodeStrokePts
WHERE (((tblCodeStrokePts.Canceled)="Y") AND ((tblCodeStrokePts.CodeStrokeDate) Between [Forms]![frmQuerySwitchboard]![txtStartdate] And [Forms]![frmQuerySwitchboard]![txtEnddate]))
GROUP BY Month([CodeStrokeDate]) & "/" & Year([CodeStrokeDate]), Year([CodeStrokeDate]), Month([CodeStrokeDate]), tblCodeStrokePts.InHouseCS
HAVING (((tblCodeStrokePts.InHouseCS)="No" Or (tblCodeStrokePts.InHouseCS) Is Null Or (tblCodeStrokePts.InHouseCS)="N"))
ORDER BY Year([CodeStrokeDate]), Month([CodeStrokeDate]);

I want to subtract TotalEDCanceled from TotalEDCalled to give me TotalEDCompleted.

I would greatly appreciate anyone's help. Thank you!

 

[jzwp22]

If you have matching data (by month/year) for both counts, you can create a third query that joins your 2 queries by the [month/year] field and do the subtraction there.

Now if you have month/year records in one query but not in the other, things get a little more complicated but there are ways of handling that as well.

 

[ambrn14]

I have matching data (by month/year) for both counts. A third query that joins my 2 queries by the [month/year] field and do the subtractions is the query that I am having trouble with.

Can you help me with the SQL for that type of query?

 

[jzwp22]

Something along these lines:

SELECT TotalEDCalled.[Total ED Called]-TotalEDCanceled.[Total ED Canceled] As EDDifference
FROM TotalEDCalled INNER JOIN TotalEDCanceled ON TotalEDCalled.[month/year]=TotalEDCanceled.[month/year]