subtracing the count values between to count queries

[john3j]

I have an access database where I have multiple queries that I have created in SQL view to produce a count number, more particularly, a count of the number of records meeting a certain criteria. One of the queries looks like the following in SQL view:

SELECT Count(*) As [Count Test]
FROM [Count Test]

My question is how do I created another query that will subtract the values produced by two queries? For instance Count Test1 - Count Test2 = Count Test3

I would appreciate any help. Thank you!

[CreganTur]

My first question for you is this: do you need this data in a query, or would it work to gather it via VBA? I ask because VBA has a function names DCount that you could use to pull different values into variables, and then perform mathematical functions with the variable values.

Otherwise, yes, it is possible to query a query. In Design view, instead of choosing a table, choose the query you want to work with. Play around with that and see if you can get it to do what you want.

[hansup]

You could try a subquery for each of your counts, then compute their difference in the "parent" query.

SELECT
a.Count1
, b.Count2
, (a.Count1 - b.Count2) AS Difference
FROM
(SELECT
Count(id) AS Count1
FROM
tblFoo
) AS a,
(SELECT
Count(id) AS Count2
FROM
tblDates
) AS b;