Subracting One SQL Query from another SQL Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pikku Apina - 31 May 2006 10:46 GMT

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

Reply to this Message

Jamie Collins - 31 May 2006 11:51 GMT

> 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
[quoted text clipped - 9 lines]
> FROM [Test RFC Log]
> WHERE ((([Test RFC Log].DateIssuedtoEDS)>=#1/1/2001#));

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

Jamie.

--

Reply to this Message

Ofer Cohen - 31 May 2006 12:16 GMT

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#")

Signature

Good Luck
BS"D

> 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
[quoted text clipped - 14 lines]
>
> Chris

Reply to this Message

John Spencer - 31 May 2006 13:08 GMT

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.

>I have a table with various data, I need to make calculations of this data
>in
[quoted text clipped - 15 lines]
>
> Chris

Reply to this Message