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);
--
Bryan Reich
Microsoft Office
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm


"Ngan Bui" <(E-Mail Removed)> wrote in message
news:70db01c3e787$596701f0$(E-Mail Removed)...
> 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?

Group the data first before counting on it.


>-----Original Message-----
>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?
>.
>