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

T

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.
 
J

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
 
R

richard

Tammy said:
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.
 
R

richard

Tammy said:
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.
 
T

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

richard

Tammy said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top