Display a count of unique records in a query in Access 2007

[Tammy]

Hi,

I'm not sure if I should be in this group, or Reports, but I'll start here.

I have a user who would like to do a count of unique records and display the
information in a query, or a report. Here are the basics:

She has a list of donors, some of the donors donate more than once to the
same cause. What she would like is a list of the number of donors, per cause.
But, if the donor ID repeats for the same cause, she does not want that ID
counted again.

Example:

We can get a query to return a count of the records per cause. It is
returning a value of "7" for a cause, however, there were actually only four
individual donors for the cause. Since some donors contributed more than
once, and their donations are considered separate transactions, the records
are entered individually (not lumping all the donations from one donor
together). We would like the result to be a count of "4" - the individuals
who donated, not "7".

Does anyone know how I can get Access 2007 to display this count correctly?
Should I include any additional information?

Thanks so much.

 

[Jeff Boyce]

Tammy

Take a look at the Totals query. It sounds like you could "GroupBy" CauseID
and "GroupBy" DonorID and "Count" DonorID.

You might need to first build a query that returns Unique Values for CauseID
and DonorID, then run the Totals query on the first query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Tammy]

Thanks for the great suggestion, Jeff! I'll give it a try!

 

[richard]

Hi,

I'm not sure if I should be in this group, or Reports, but I'll start
here.

I have a user who would like to do a count of unique records and display
the
information in a query, or a report. Here are the basics:

She has a list of donors, some of the donors donate more than once to the
same cause. What she would like is a list of the number of donors, per
cause.
But, if the donor ID repeats for the same cause, she does not want that ID
counted again.

Example:

We can get a query to return a count of the records per cause. It is
returning a value of "7" for a cause, however, there were actually only
four
individual donors for the cause. Since some donors contributed more than
once, and their donations are considered separate transactions, the
records
are entered individually (not lumping all the donations from one donor
together). We would like the result to be a count of "4" - the individuals
who donated, not "7".

Does anyone know how I can get Access 2007 to display this count
correctly?
Should I include any additional information?

Thanks so much.

 

[richard]

Hi,

I'm not sure if I should be in this group, or Reports, but I'll start
here.

I have a user who would like to do a count of unique records and display
the
information in a query, or a report. Here are the basics:

She has a list of donors, some of the donors donate more than once to the
same cause. What she would like is a list of the number of donors, per
cause.
But, if the donor ID repeats for the same cause, she does not want that ID
counted again.

Example:

We can get a query to return a count of the records per cause. It is
returning a value of "7" for a cause, however, there were actually only
four
individual donors for the cause. Since some donors contributed more than
once, and their donations are considered separate transactions, the
records
are entered individually (not lumping all the donations from one donor
together). We would like the result to be a count of "4" - the individuals
who donated, not "7".

Does anyone know how I can get Access 2007 to display this count
correctly?
Should I include any additional information?

Thanks so much.

 

[Tammy]

Jeff,

Thanks so much for your suggestion - it worked prefectly! And, yes, I did
have to create a query that first retuned unique values, and then based the
count on that query.

I knew this could be done, just ran into a brain block when I was trying to
figure it out. Thanks again, and have a great week!

 

[richard]

Hi,

I'm not sure if I should be in this group, or Reports, but I'll start
here.

I have a user who would like to do a count of unique records and display
the
information in a query, or a report. Here are the basics:

She has a list of donors, some of the donors donate more than once to the
same cause. What she would like is a list of the number of donors, per
cause.
But, if the donor ID repeats for the same cause, she does not want that ID
counted again.

Example:

We can get a query to return a count of the records per cause. It is
returning a value of "7" for a cause, however, there were actually only
four
individual donors for the cause. Since some donors contributed more than
once, and their donations are considered separate transactions, the
records
are entered individually (not lumping all the donations from one donor
together). We would like the result to be a count of "4" - the individuals
who donated, not "7".

Does anyone know how I can get Access 2007 to display this count
correctly?
Should I include any additional information?

Thanks so much.