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

[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!

 

[Guest]

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.

 

[Guest]

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

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!

 

[Guest]

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

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

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!

 

[Guest]

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

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

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

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!