Solved

Unique Count

Posted on 2003-10-22
4
1,747 Views
Last Modified: 2012-06-27
Hi,                              
I have pasted some sample data. I am trying to get a unique count of the months per person. ID is unique and the primary key. More details below.                              
                              
                              
ID                 FIRSTNAME      LASTNAME        Month         VISITS        
2296015955      MAX      SMART      Feb-03      1
2296015955      MAX      SMART      Jan-03      2
2296015955      MAX      SMART      May-02      2
2296015955      MAX      SMART      Oct-02      2
2296016405      JOHN      SMART      Jun-02      2
2296016405      JOHN      SMART      May-03      2
2296086814      TEX      JONES      May-03      2
2296088711      FRANK      FLOYE      Jan-03      6
2296088711      FRANK      FLOYE      Mar-03      2
2296088711      FRANK      FLOYE      May-02      2

                              
Here you can see Max Smart has had 1 visit in Feb 2003, 2 in Jan 2003 2 in May 2002 and                              
2 in October 2002. I got this by doing a Count function in Access.                              
What I need now is to find out the number of months Max Smart had visits in.                               
In this case it would be 4 ( Jan 03, Feb 03, May 02 and Oct 02)                              
For John Smart it would be 2 ( June 02 , May 03)                              
For Tex Jones it would be 1 ( May 03)                              
For Frank Floye it would be 3 ( Jan 03, March 03, May 02)                              
                              
I cannnot do a Count for this because a person may have multiple visits in a month.                              
My goal is to find the number of months per person. How can I do this in Access.                              
Would appreciate any help.                              
Thanks,                              
GuruGanta                              
0
Comment
Question by:guruganta
  • 2
4 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 9600495
Create a query that groups on Id, FirstName, LastName, and Month
(add those fields and Click the Sigma in the toolbar)
Add an additional field called Month.
In the total row for the second Month column, change it to "Count"

That should do it
Walt
0
 
LVL 17

Expert Comment

by:walterecook
ID: 9600504
Whoops I lied.
However that query is still useful.  Use that query as a source for another query.  In the new query do a count on months.

Sorry for the mixup
Walt
0
 
LVL 1

Accepted Solution

by:
rikky_b earned 250 total points
ID: 9600658
Hi GuruGanta,
Here's a query statement you can use

SELECT DISTINCT Count(Month([month])) AS CountofMonthsVisited, visit_table.id, visit_table.firstname, visit_table.lastname
FROM visit_table
GROUP BY visit_table.id, visit_table.firstname, visit_table.lastname;

Note that I used a fictional table name 'visit_table' which has an identical structure to the data you provided above.
Let me know if this isn't exactly what you're looking for.

Eric
0
 

Author Comment

by:guruganta
ID: 9602088
Eric,

Thanks for the input Walter and Eric. I was able to apply Eric's solution. I am awarding him the points.
I appreciate the help, Walter and Eric.
Muchos Gracias.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now