guruganta
asked on
Unique Count
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
(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