Counting records in a report from table source w/out duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that counts items from 4 separate tables that are created
using a macro that runs make 4 table queries.

Each item is counted using a DCount expression for example:

=DCount("[SSNumber]","tblIntakeBarriers","[PARTIndividualwDisabilty2] = 'Y'")

My problem is that a person (SSNumber) can have multiple items that equal Y.

I want to count the SSNumber only ONCE from each table; so I have an
unduplicated count of clients.

Is this possible?

Thx!
 
Create a group by Query on the table tblIntakeBarriers for the two fields:
SSNumber and PARTIndividualwDisabilty2.
SELECT SSNumber , PARTIndividualwDisabilty2
FROM tblIntakeBarriers
GROUP BY SSNumber , PARTIndividualwDisabilty2


Run the dcount on the query you created.
 
Please forgive my ignorance but do you mean create a new query in Design mode
and add just those two fields from the tblIntakeBarriers? I'm not clear on
how to create a 'Group By' query.

thx for the quick response,
E
--
Youhooo


Ofer said:
Create a group by Query on the table tblIntakeBarriers for the two fields:
SSNumber and PARTIndividualwDisabilty2.
SELECT SSNumber , PARTIndividualwDisabilty2
FROM tblIntakeBarriers
GROUP BY SSNumber , PARTIndividualwDisabilty2


Run the dcount on the query you created.

Youhooo said:
I have a report that counts items from 4 separate tables that are created
using a macro that runs make 4 table queries.

Each item is counted using a DCount expression for example:

=DCount("[SSNumber]","tblIntakeBarriers","[PARTIndividualwDisabilty2] = 'Y'")

My problem is that a person (SSNumber) can have multiple items that equal Y.

I want to count the SSNumber only ONCE from each table; so I have an
unduplicated count of clients.

Is this possible?

Thx!
Click to expand...
Click to expand...
 
Yes, and you can copy and paste the SQL I gave you, it should work.
If not I'll give you step by step instruction hoe to do that

Youhooo said:
Please forgive my ignorance but do you mean create a new query in Design mode
and add just those two fields from the tblIntakeBarriers? I'm not clear on
how to create a 'Group By' query.

thx for the quick response,
E
--
Youhooo


Ofer said:
Create a group by Query on the table tblIntakeBarriers for the two fields:
SSNumber and PARTIndividualwDisabilty2.
SELECT SSNumber , PARTIndividualwDisabilty2
FROM tblIntakeBarriers
GROUP BY SSNumber , PARTIndividualwDisabilty2


Run the dcount on the query you created.

Youhooo said:
I have a report that counts items from 4 separate tables that are created
using a macro that runs make 4 table queries.

Each item is counted using a DCount expression for example:

=DCount("[SSNumber]","tblIntakeBarriers","[PARTIndividualwDisabilty2] = 'Y'")

My problem is that a person (SSNumber) can have multiple items that equal Y.

I want to count the SSNumber only ONCE from each table; so I have an
unduplicated count of clients.

Is this possible?

Thx!
Click to expand...
Click to expand...
Click to expand...
 
Ok - I got it; I was over-complicating this. Thx!
--
Youhooo


Ofer said:
Yes, and you can copy and paste the SQL I gave you, it should work.
If not I'll give you step by step instruction hoe to do that

Youhooo said:
Please forgive my ignorance but do you mean create a new query in Design mode
and add just those two fields from the tblIntakeBarriers? I'm not clear on
how to create a 'Group By' query.

thx for the quick response,
E
--
Youhooo


Ofer said:
Create a group by Query on the table tblIntakeBarriers for the two fields:
SSNumber and PARTIndividualwDisabilty2.
SELECT SSNumber , PARTIndividualwDisabilty2
FROM tblIntakeBarriers
GROUP BY SSNumber , PARTIndividualwDisabilty2


Run the dcount on the query you created.

:

I have a report that counts items from 4 separate tables that are created
using a macro that runs make 4 table queries.

Each item is counted using a DCount expression for example:

=DCount("[SSNumber]","tblIntakeBarriers","[PARTIndividualwDisabilty2] = 'Y'")

My problem is that a person (SSNumber) can have multiple items that equal Y.

I want to count the SSNumber only ONCE from each table; so I have an
unduplicated count of clients.

Is this possible?

Thx!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top