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

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
Utf
10/18/2007 5:59:02 PM
access.queries 6343 articles. 1 followers. Follow

6 Replies
4011 Views

Similar Articles

[PageSpeed] 23

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
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
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
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
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
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
richard
10/26/2007 3:58:25 AM
Reply:

Similar Artilces:

Range created using Union...accessing cells
In trying to use the ranges created from Union, I can't refer to the cells properly. For example, R1=Union(myrange1, myrange2) If I try the following it works fine: Dim c as range For each c in R1.Cells msgbox c.Address Next If I try the following the second iteration when I=2 gives me the address of the row in the worksheet right below R1.Cells(1,1). It doesn't give me the address of the second row, fist column cell of R1: Dim I As long For I = 1 to R1.Cells.Count msgbox R1.Cells(I, 1).Address Next I Your union is not quite right... You missed the s...

Display date in spanish
I am using a spreadsheet in a mail merge letter in Word. The front side is in English and the back side in Spanish. I need to display the mailing date in Spanish. Is there a way to convert the English text date (December 15, 2009) into Spanish? I've tried using 2 cells - one for the English and one formatted with a "Date" format and changing the language to Spanish (Mexico). The date appears in Spanish in Excel but the number format of the date (12/15/2009) appears on the letter. Hi "mbparks" <mbparks@discussions.microsoft.com> wrote in mes...

Outlook 2007/2010 Printing Issues
A general question to pick the brains of any Office gurus out there, mainly to assess whether or not these requests are at all achievable. I have a handful of users that do not want to migrate from Outlook 2003 because of the way the calendar is printed and their refusal of change. The main issues are as follows: - Need to combine Saturday and Sunday into one column, as it does in 2003, but has not been achievable in 2007 or 2010 that I've seen. Have tried third party apps to no avail. - Each day needs to be able to hold a multitude of appointments. An example I am lookin...

How to import DATA from SQL2K to Access?
I am using get external data, import, ODBC type and it's error out saying I cannot use ODBC to import/export data. I would appreciate if someone please show me the better way of importing data from sql. Mehbs, Two ways. 1 - From SQL Server, use DTS to copy the data from the SQL Server table to the Access table. 2 - From Access, link your SQL Server table through ODBC and then use an append query to copy the data from the linked SQL Server table to your Access table. Good luck. -- Sco M.L. "Sco" Scofield, MCSD, MCP, MSS, A+, Access MVP 2001 - 2005 Denver Area Ac...

Money 2007 Canadian Version
Anyone if and when a canadian version will come out and also which version it will be? In microsoft.public.money, Purzel wrote: >Anyone if and when a canadian version will come out and also which version >it will be? > There is no longer a Canadian specific product. Canadians can actually use any version. They will get Canadian banks, they just won't get other Canadian specific info (taxes, RRSP vs 401k, etc). If they use a US term such as IRA, they can parallel many of the functions. I expect this will be some problem for tax preparation in that the basis rules for Ca...

Analytical Accounting Account Access to multiple Dimensions
In Account Access to Transaction Dimension Codes window, we are able to assign dimension codes by specifying a particular account segment. Can we include the functionality to specifying using multiple account segments? Thanks. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message...

Display on the cell with a value
Hi All I need help with a formula, I have 3 cell columns B,C & D with vlookup�s in each. The way I have set it up so that only one of the look ups will work. How do then make a formula that will be in column E and look columns B,C&D and display the only number that has worked Eg. A B C D AT111 #N/A 0.71 #N/A Please help Thanks Andrew -- koba ------------------------------------------------------------------------ koba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28639 View this thread: http://www.excelforum.com/showthread.php?threadid=501901 ...

counting words
I'm trying to count how many times the word, "false" appears in a coloumn range of: F1:F2500 I've formated the "F" coloumn to analize and put either a, false or true, if the coloumns, "A1:2500 and B1:2500" do or don't match. Now I'd like to count, sort, find all the falses. Too many with a list of 2500. I've thought there was a way to also deliniate and show which row/cell each of the falses are in. This would help me tremendously as once I count them I'll know how many and where I need to update. I'm sure t...

Project Server 2007 Login issue
While logged into Project server as myself, our system admin used the Sign In as Different User option to log in and check a config setting that I did not have access to view. PWA recognized her, and when she finished, I used the same option to log back in as myself. Now each time I access PWA, I am logged in as her with admin privileges. I have deleted temporary internet files, shut down and restarted my computer several times and still have her PWA profile. How do we fix this? Try checking your Managed Passwords under your Control Panel>User Accounts. It could be her wind...

Excel 2007 Smart Forms
Is there a web site that I can go to get find Excel 2007 Smart forms for free? I am trying to create a user form that will not let a user go on to the next question or worksheet without filling out all required information first. I would also like it to not let a user hit the submit until all required information is filled out. I am new to VBA and don't even know if I need to use VBA to make my above requirements work. Any help would be greatly appreciated. Keven ...

newbie select query in form problem
Hi, I'm trying to have a datasheet form based on a query in a subform. The main form contains 3 unbound textboxed that will be used to search. once they are filled I want to press a button then the query will execute showing the results if any. What I have now is the subforms loads up now as soon as I open the form with all records and becuase the BackEnd is in a Share drive it takes forever to load up. so what I need is to not show anything but when I actually execute the "Search" button. is this possible? thanks for your help. Absolutely! -Go to your main form in des...

How to enter a long address to new web query
sup pros something like this http://tinyurl.com/e2n5e when i paste it , i get the message:- The text string you entered is too long. Reduce the number of characters used or cancel the entry -- Pivotrend ------------------------------------------------------------------------ Pivotrend's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4062 View this thread: http://www.excelforum.com/showthread.php?threadid=400401 You should be able to get rid of http:// Unless you are still using IE2 or 3 "Pivotrend" wrote: > > sup pros > something like th...

easy way around "not updateable query" for sums?
I have a table. I am trying to update that table based on a query. The query involved used sums. When I try to this, I get "this is not an updatable query." I know the cause for it, the fact that the query has sums. I also know the way around it. Run my query as a make table query, then build my update query from that. This project would have me doing this a couple of dozen times. I really do not want to build 24 make table queries, 24 update queries, and then have to delete those 24 tables. If nothing else, I would expect that would seriously fragment my databse,...

where is the "on timer" function for forms in Access 2007?
In my first run-through looking for the "on timer" function in Form Properties, I coudn't find it in Access 2007. I saw something about the "sandbox" mode and some functions are disabled in the same and I hope that has nothing to do with it. On Timer is still there. Are you certain you're looking at the properties for the Form and not a section or control on the form? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "jack" <jack@discussions.microsoft.com> wrote in message news:96346...

Limits of MS Access 2003
A quick question: What are the capacity limits of MS Access 2003? Background: We are using MS Access at our company and I believe our database has grown to 9 Gb. I'm being told that we are near the limits of MS Access. In fact I was referred to a web page (http://www.earthskater.net/devzone/access-specifications.asp - not Microsoft's) that states the limits for MS Access 2003 as 2 Gb. So I wonder if we really have 9 Gb worth of data in the MS Access database or if the website is actually wrong. -- Joe "Joe Chambers" <joseph.chambers@comcast.net> wrote ...

VBA
I would really appreciate any help. How can I open a second instance of access and require that be closed before the user can return to the first instance from which it was called? Thank you Hi Use a shell call with (after shell) docmd.quit on the 1st DB (or the 2nd if you want to go back to the 1st) - hope that makes sense. -- Wayne Manchester, England. "Programmer - wannaB" wrote: > I would really appreciate any help. > How can I open a second instance of access and require that be closed before > the user can return to the first instance from which it was call...

Outlook 2007 stationary
How does one create new stationary in 07? I have looked everywhere and what was easy under former programs seems hidden now. thanks, There is no stationery creation tool in Outlook 2007 itself. See = http://turtleflock-ol2007.spaces.live.com/blog/cns!C1013F1F9A99E3D8!230.e= ntry --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/article.aspx?id=3D54 =20 "Bill" <...

BCM 2007 vs CRM
I just got a call from a CRM prospect looking for a comparrison between Dynamics CRM 3.0 and Business Contact Manager 2007 (aka BCM 2007). They have a 25 person sales force and were looking at CRM for Contact management, quote/opportunity tracking and future integration with their ERP system (GP). I have seen what is at the BCM 2007 web site and it sounds comparable so I can understand why the customer is asking why should they buy CRM when BCM is included in Office. Has anyone seen anything that compares the 2 products? Thanks Best way - set up a Demo Have you downloaded the CRM VP...

Cpu 100%
A client told me that it was slow in its app since he upgraded to Office 2007 Pro. I opened the task manager to see the performance... After checking it, I can say that each time I type a character the CPU goes up between 15 and 95%... Writing a phrase takes a long time because it appears a little while after typing it. Same thing if I move the mouse over a field. 100% is reached very easily. Virus check - Done Spyware check - Done Macro Disabled - Done I don't seem to have the problem in Word or Outlook. I noticed something else, if I move the task manager or any other window ove...

New to Access 03-05-08
I am new to access and would like to design a database that will automatically email certain documents when the count down reaches a designated number. "carlos24mar" <carlos24mar@discussions.microsoft.com> schrieb im Newsbeitrag news:1FD00A4F-2FBB-4008-B8F8-44C05342B0FC@microsoft.com... > I am new to access and would like to design a database that will > automatically email certain documents when the count down reaches a > designated number. On Wed, 5 Mar 2008 09:00:01 -0800, carlos24mar <carlos24mar@discussions.microsoft.com> wrote: >I am new to acc...

RMS 2.0 SP2 "does not enable remote access"
In the Microsoft article "How Microsoft Dynamics RMS can help with PCI compliance", there is a single mention of "Microsoft Dynamics RMS does not enable remote access". Unfortunately, the article doesn't explain exactly what this means. Does this mean after installing RMS SP2 that I won't be able to administer our Server remotely? Or is RMS itself somehow restricted? Or the POS's? I'm sorry Microsoft--this sentence raises more questions that it explains. thanks, randy This is a multi-part message in MIME format. ------=_NextPart_000_027A_01C8D...

Where is the Stop Recording toolbar?
When I record a macro, the Stop Recording toolbar does not appear. There is no 'Macro' toolbar listed in View / Toolbars. How can I 'show' this toolbar? When recording a macro from the keyboard, is there any way to select 'Relative Reference' without this toolbar? Thanks! Go to view, toolbars, and select Visual Basic. -- crispbd ------------------------------------------------------------------------ crispbd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=10880 View this thread: http://www.excelforum.com/showthread.php?threadid=...

Outlook 2007 Address book 04-18-10
I imported contacts into Outlook. They went into the contacts area but when I bring up a new email and hit the TO: button the contacts do not show up. I tried checking the box to make them the address book but it is greyed out. Any suggestions would be appreciated. Thanks Need more information. Why did you import these Contacts? Were they not in Outlook format? Importing is not the correct way to transfer Outlook data. Do these Contacts have valid resolved electronic addresses? Have you added the Outlook Address Book Service to this profile? Have you at any time used Windows Easy...

Too Many Fields
I have a report that pulls from a single query. This query is made up of two tables and 9 queries. The tables include: 1) Employee information that requires about 45 unique fields 2) Company information that requires about 55 unique fields The 9 queries are simple queries that basically group information from various other tables including the employee and company table. My database is normalized. Here is the issue: The amount of information I need to display on a particular report encompasses about 70 fields. This translates into requiring 70 fields in the query powering the report....

Make a report to count how many records with the Report Wizard
I pass few hours triyng to figure it out how to make a simple report with the Report wizard in the CRM online. Here is the need. I have a field in Lead call Market. I like to know how many lead I've got by Market. That is it. I've try grouping and count on grouping. I can't have a summary. All the line come in my report. My DB contain more than 10 000 records. So I'cant make an Excel report. Do you have any idea how to make it? Please not that we are on CRM Online Thanks On Feb 3, 11:07=A0am, wesmike <wesm...@discussions.microsoft.com> wrote: > I pass few hour...