Counting unique values in main table

  • Thread starter Thread starter Ngan Bui
  • Start date Start date
N

Ngan Bui

I have two tables, tblComplaint linked one to many to
tblCompResponse.

In one summary report, they want a count of complaints
where the response is not closed (responsedate is not
null) and another count where the response is closed.

If a complaint has two responses that are closed, that
complaint should be counted only once.

On the same report, they want to count the complaints that
have the investigation done (investclosedate is not null),
but the final response isn't (responsedate is null). but
you can have two responses that are done and I only need
it to be counted once.

I tried a query where the two tables are linked...but then
it would count a comp twice if it has multiple responses.

Is there any easy way to do this?
 
Select only the fields you are interested in, do not select the unique IDs
for the response table as returned values, and then write the query starting
as follows:

SELECT DISTINCT *other stuff goes here*

This should remove any return rows that are duplicates.

ExampleQuery:

SELECT DISTINCT PrimaryTable.string
FROM PrimaryTable, SecondaryTable
WHERE PrimaryTable.ID=SecondaryTable.primID and NOT
IsNull(SecondaryTable.someDate);
 
Back
Top