Subracting One SQL Query from another SQL Query

[Guest]

I have a table with various data, I need to make calculations of this data in
many ways. I currently have two queries that are doing a count of the data
shown below and I want to subtract one from the other for a seperate query
for a report. Any ideas?

Query 1
SELECT COUNT(*) AS CompletedSubmittedIA
FROM [Test RFC Log]
WHERE (([Test RFC Log].DateRMCompletedIAforFinance)>=#1/1/2001#);

Query 2
SELECT COUNT(*) AS IASubmittedtoEDS
FROM [Test RFC Log]
WHERE ((([Test RFC Log].DateIssuedtoEDS)>=#1/1/2001#));

Many thanks for any help

Chris

 

[Jamie Collins]

I currently have two queries that are doing a count of the data
shown below and I want to subtract one from the other for a seperate query
for a report. Any ideas?

Query 1
SELECT COUNT(*) AS CompletedSubmittedIA
FROM [Test RFC Log]
WHERE (([Test RFC Log].DateRMCompletedIAforFinance)>=#1/1/2001#);

Query 2
SELECT COUNT(*) AS IASubmittedtoEDS
FROM [Test RFC Log]
WHERE ((([Test RFC Log].DateIssuedtoEDS)>=#1/1/2001#));

SELECT Query2.IASubmittedtoEDS - Query1.CompletedSubmittedIA
AS result
FROM Query1, Query2;

Jamie.

--

 

[Guest]

In a text field control source in the report you can write.

=Dlookup("CompletedSubmittedIA","Query1") -
Dlookup("IASubmittedtoEDS","Query2")

Or, instead of using two queries you can write
=Dcount("*","[Test RFC Log]","[DateRMCompletedIAforFinance])>=#1/1/2001#") -
Dcount("*","[Test RFC Log]","[DateIssuedtoEDS])>=#1/1/2001#")

 

[John Spencer]

How about one query?

SELECT Abs(Sum(DateRMCompletedIAforFinance>=#1/1/2001#)) -
Abs(Sum(DateIssuedtoEDS>=#1/1/2001#)) as MyDiff
FROM [TEST RFC LOG]
WHERE [Test RFC Log].DateRMCompletedIAforFinance>=#1/1/2001#
OR [Test RFC Log].DateIssuedtoEDS)>=#1/1/2001#

Or you can use the DCount function mentioned elsewhere in this thread.