How to count 2 datasets and then subtract the difference?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jon - 25 Apr 2008 20:32 GMT

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?

Reply to this Message

KARL DEWEY - 25 Apr 2008 21:00 GMT

Post the first two queries with their names,

Signature

KARL DEWEY
Build a little - Test a little

> 1) I need to be able to count the number of records returned by 2
> queries (which I can do).
[quoted text clipped - 16 lines]
> as expected. So why doesn't it work by nesting queries that should
> return integers?

Reply to this Message

John Spencer - 28 Apr 2008 12:26 GMT

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

Reply to this Message

Jon - 28 Apr 2008 16:11 GMT

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

> 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)
[quoted text clipped - 18 lines]
> Center for Health Program Development and Management
> University of Maryland Baltimore County

Reply to this Message