Aggregate Query Returns No Values

[Xiaoding]

I have three queries, A, B, and C. Each query uses Dcount to return the number of records found by each query.



In addition, I have an aggegate query, that takes the record count from each of these queries. So, I get a record count of A, B, and C, and I make a total from that.

The problem is, is when A, B, or C return no value, or no records found. Then the aggregate query returns no values at all, even though two of the queries may return a number, but the third does not.

I guess the question I have is, how can I get the aggregate query to deal with no result from one of the queries?? Or get the originating queries to give a zero when there are no records found?

thnx!

[RuralGuy]

Try using the Nz() function.

[Xiaoding]

Ok, tried that, it didn't work.

When I run query A, and it returns no value, it returns no value for all the fields, not just the one I'm using for the count. So, there are six fields in query A, and they are all blank, or no row present.

I'm not sure where to use the NZ function, I tried it in the aggregate and the originating query. Not sure if the other blank fields are the problem, or not.

In the aggregate query, I have a field called "CL2", which is the count from the originating query H2. I tried NZ (CL2), in the aggregate query, in the column header, it didn't work, did I do it correctly?

[RuralGuy]

We're just taking wild guesses here. How about post the SQL for the queries with descriptions as to what works and what doesn't?

[Xiaoding]

Here is the SQL for one of the originating queries:

SELECT DCount("TAG_NUMBER","h pm's closed, 2nd") AS CL2, aims_WKO.WO_NUMBER, aims_WCT.DATE_DONE, aims_WKO.DUE_DATE, aims_WKO.TAG_NUMBER, aims_WKO.WO_PROBLEM
FROM ((aims_WKO INNER JOIN aims_EQU ON aims_WKO.TAG_NUMBER = aims_EQU.TAG_NUMBER) INNER JOIN aims_Manufacturer ON aims_EQU.MANUFACTUR = aims_Manufacturer.CODE) INNER JOIN aims_WCT ON aims_WKO.WO_NUMBER = aims_WCT.WO_NUMBER
WHERE (((aims_WCT.DATE_DONE) Between [forms]![H PM's Date Criteria, Completion Report]![tx cl2a] And [forms]![H PM's Date Criteria, Completion Report]![tx cl2b]) AND ((aims_WKO.DUE_DATE) Like [forms]![H PM's Date Criteria, Completion Report]![tx tr1]) AND ((aims_WKO.WO_TYPE) Like "PM"))
ORDER BY aims_WCT.DATE_DONE DESC;

Here is the sql for the aggregate query:

SELECT DISTINCT [How many PM's created in a month].TR1, [H Pm's Closed, 1st].CL1, [H Pm's Closed, 2nd].CL2, [H Pm's Closed, 3rd].CL3, [H Pm's Closed, Beyond the 3rd Month].CL4, Val([CL4]) AS N4, Val([CL1]) AS N1, [n1]+[n4]+[n2]+[n3] AS [T Closed], Val([CL2]) AS N2, Val([CL3]) AS N3
FROM [H Pm's Closed, 1st], [H Pm's Closed, 2nd], [H Pm's Closed, 3rd], [How many PM's created in a month], [H Pm's Closed, Beyond the 3rd Month];

I have been using CL2 as the "no result" test. I make CL2 return no result;, thus the query that CL2 is in returns no result in any of it's fields, and then, when I run tha aggreagate query, it too returns no result fo CL2, and no other results as well, even though the other 4 originating queries do return results.

thnx!

[RuralGuy]

Putting a DCount() function in a query *has* to make it run like a slug. It has to do that aggregate function for every record. Maybe someone with better SQL skills than mine will drop by with some suggestions.

[Xiaoding]

Gee, the speed is fine, it's a small database, less than 20,000 records. It's getting the data, when one query returns nothing, that's the problem.

I think I'm going to try populating a table, query by query, and then getting the record counts from that.

thnx for your help!