How to count 2 datasets and then subtract the difference?

[Jon]

1) I need to be able to count the number of records returned by 2
queries (which I can do).
2) Then I need to subtract one count from the other and return that
number. Simple, right? When I try to do this, I get the error 'Query
input must contain at least one table or query'.

Here's the query:

SELECT
( SELECT * FROM [Level 01 Count Distinct Customers During Billing
Period])
-
(SELECT * FROM [Level 01 Count Distinct Customers During Billing
Period Only])


If I execute either of the inner selects, integers are returned as
expected. The funny thing is - if I were to execute 'SELECT (0) -
(1)' , in other words, hard coding integers, the query would return -1
as expected. So why doesn't it work by nesting queries that should
return integers?

 

[KARL DEWEY]

Post the first two queries with their names,

 

[John Spencer]

I get a different error when I attempt something similar. The error I get is
-3025 Reserved Error; There is no message for this error. (Access 2003)

SELECT (SELECT Count(*) From FAQ)-(SELECT Count(*) FROM FAQ0) AS Expr1

I can do the following and it works.

SELECT Top 1 (SELECT Count(*) From FAQ)-(SELECT Count(*) FROM FAQ0) AS Expr1
FROM [AnyTable]

And of course you could always use the DCount function (the following works)
SELECT DCount("*","FAQ") - DCount("*","FAQ0")

Why does the first one fail? Ask the developers as Microsoft as they are the
only ones that M I G H T know. I say "might" because the developer that
developed the code may no longer be available - left the team, left Microsoft,
etc.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Jon]

Thank you, John! What worked for me was using your DCOUNT example. -
Jon