Eliminating Duplicates - Subtracting COUNT values

M

MValentine

I had been trying to find a way to eliminate duplicates from a query. I
finally came up with the idea of creating 2 queries. Query 1 contains ALL
entries and query 2 contains all duplicates. Each query is composed of 2
fields; COID and COUNT. I am wanting to create a new query which matches the
COID from query 2 with the COID in query 1 and subtracts the query 2 COUNT
from the COUNT found in query 1. Query 1 contains more COID entries than
query 2. I want all COID entries from query 1 to be listed in my final query.
If a similar COID is not found in query 2 then I want the COUNT value found
in query 1 to be displayed in my final query. I have apprximatly 70 COID
types. I have been able to create a query which groups and subtracts COUNT
values correctly. However my problem is that I can not get it to display the
COUNT value in query 1 if their is no similar COID in query 2. It will list
the COID, however the COUNT value is blank.
Below is my SQL syntex:
SELECT COIDSummaryQ.COID, [CountOfCOID]-[CountOfAllDSLAndWLQ_COID] AS TOTAL
FROM COIDSummaryQ LEFT JOIN DuplicateCOIDandPairSummaryQ ON
COIDSummaryQ.COID = DuplicateCOIDandPairSummaryQ.AllDSLAndWLQ_COID
GROUP BY COIDSummaryQ.COID, COIDSummaryQ.CountOfCOID,
DuplicateCOIDandPairSummaryQ.AllDSLAndWLQ_COID,
DuplicateCOIDandPairSummaryQ.CountOfAllDSLAndWLQ_COID;

Thanks for any suggestions.
 
Top