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.
|
|
0
|
|
|
|
Reply
|
 Utf
|
10/18/2007 5:59:02 PM |
|
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" <Tammy@discussions.microsoft.com> wrote in message
news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> 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.
|
|
|
1
|
|
|
|
Reply
|
Jeff
|
10/18/2007 6:18:13 PM
|
|
Thanks for the great suggestion, Jeff! I'll give it a try!
"Jeff Boyce" wrote:
> 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" <Tammy@discussions.microsoft.com> wrote in message
> news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> > 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.
>
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
10/18/2007 7:25:01 PM
|
|
"Tammy" <Tammy@discussions.microsoft.com> wrote in message
news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> 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.
|
|
|
0
|
|
|
|
Reply
|
richard
|
10/21/2007 4:41:18 AM
|
|
"Tammy" <Tammy@discussions.microsoft.com> wrote in message
news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> 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.
|
|
|
0
|
|
|
|
Reply
|
richard
|
10/21/2007 4:50:01 AM
|
|
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!
"Jeff Boyce" wrote:
> 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" <Tammy@discussions.microsoft.com> wrote in message
> news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> > 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.
>
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
10/23/2007 1:41:01 PM
|
|
"Tammy" <Tammy@discussions.microsoft.com> wrote in message
news:B49F52DC-E610-490F-9C20-8EBB84E21FD3@microsoft.com...
> 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.
|
|
|
0
|
|
|
|
Reply
|
richard
|
10/26/2007 3:58:25 AM
|
|
|
6 Replies
1658 Views
(page loaded in 0.003 seconds)
Similiar Articles: Count Unique Values in a Totals Query - microsoft.public.access ...-- John Spencer Access MVP 2002-2005, 2007 Center for Health ... Count Unique Values in a Totals Query - microsoft.public.access ..... number of unique 'Manufacturer' records ... Query and Count no records Want a Zero - microsoft.public.access ...Count Unique Values in a Totals Query - microsoft.public.access ... 0 ... Display a count of unique records in a query in Access 2007 ... > and ... that contains the ... Count of Unique Values in Access Field - microsoft.public.access ...Display a count of unique records in a query in Access 2007 ... Count of unique values in Access query field ... Reports - Count ... single record ... in Access 2007. ... How do I unhide queries in Access 2007? - microsoft.public.access ...Display a count of unique records in a query in Access 2007 ... How do I unhide queries in Access 2007? - microsoft.public.access ... count the number of unique records in ... count the number of unique records in a field within Access ...... do I count the number of unique records in a field within Access? ... ... MVP 2002-2005, 2007 ... Count Unique Records Hello, I am new to Access and trying to run a query that will ... How can I count unique values in a query in the report footer ...12/18/2007 1:07:01 AM ... unique values in Access query ... can I count unique instances of repeating data on a report ... ... footer a count of the number of records ... Show ... Show only unique records in duplicated field - microsoft.public ...Action Query (Append) didn't add records due to key violations ... Show only unique ... of the number of records ... Show only unique ... Access: Show only unique records in ... Unhide query - microsoft.public.accessDisplay a count of unique records in a query in Access 2007 ... How do I unhide queries in Access 2007? - microsoft.public.access ... count the number of unique records in ... Query performance Access 2007 - microsoft.public.access.queries ...... public.access.queries ..... correlated subquery, but I tend to avoid those if possible for performance ... Display a count of unique records in a query in Access 2007 ... using count in a query - microsoft.public.accessI have a query that I am using to show all records ... using access 2007, i have a query ... How can I count unique values in a query in the report footer ... Am using Access ... Display a count of unique records in a query in Access 2007 DataBaseHi, 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 MS Access: Only show unique values in a query in Access 2007In Access 2007, I've created a query and I only want to display the unique values. How to Count Unique Records in a Microsoft Access Database | eHow.comHow to Count Unique Records in a Microsoft Access Database. ... Exit the “Show Table” dialog ... How to Query for Unique Records in Access 2007 How to Do the Count Function in Access 2007 | eHow.com... query. Access 2007 has integrated the Count ... to Count Unique Records in a Microsoft Access ... Query in Access 2007. A query in Microsoft Access is a request to display ... Count data by using a query - Access - Office.com... count data by using a Total row or by using a totals query, and view the Aggregate function reference in Access 2007. ... Query Design. In the Show ... query. Count records in ... 7/17/2012 12:31:02 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
16 followers. 