By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,133 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Need Help On How To Do Counts by Uniques

P: 3
This sounds simple, so I'm sure I'm just not getting it on how to use Access. I'm using Access 2007

What I'm trying to do is get a unique count of People by ProjectName

Example of the data set

ProjectName | Person | Action
Opus | John | Piano
Violet | Mark | Violin
Violet | Mark | Guitar
Mozart | Sandy | Flute
Mozart | Mark | Violin
Mozart | Mark | Guitar

The result should look like

ProjectName | Count of People
Opus | 1
Violet | 1
Mozart | 2

Can anyone help?

Thanks

Raymon
Aug 13 '08 #1
Share this Question
Share on Google+
3 Replies


rsmccli
P: 52
This sounds simple, so I'm sure I'm just not getting it on how to use Access. I'm using Access 2007

What I'm trying to do is get a unique count of People by ProjectName

Example of the data set

ProjectName | Person | Action
Opus | John | Piano
Violet | Mark | Violin
Violet | Mark | Guitar
Mozart | Sandy | Flute
Mozart | Mark | Violin
Mozart | Mark | Guitar

The result should look like

ProjectName | Count of People
Opus | 1
Violet | 1
Mozart | 2

Can anyone help?

Thanks

Raymon
You will want to create a query and use aggregate functions. If you create a query on your table(s) in the graphical query designer, right click in the bottom part and select 'Totals'. Another row of choices will appear. This one's pretty simple, so I would just fool around with the 'Group By' and 'Count' choices. It will be good practice since you are just starting out.

hth,
rsmccli
Aug 13 '08 #2

P: 3
You will want to create a query and use aggregate functions. If you create a query on your table(s) in the graphical query designer, right click in the bottom part and select 'Totals'. Another row of choices will appear. This one's pretty simple, so I would just fool around with the 'Group By' and 'Count' choices. It will be good practice since you are just starting out.

hth,
rsmccli
Hi! Thanks for replying back.

Any idea on how the SQL would look like?
Aug 13 '08 #3

P: 3
For the benefit of other users, I got help from someone else. It was simpler than what I thought it was.

Expand|Select|Wrap|Line Numbers
  1. SELECT ProjectName, COUNT(*) As CountOfPeople
  2. FROM (SELECT ProjectName, Person FROM tblTableName GROUP BY ProjectName, Person)
  3. GROUP BY ProjectName 
  4.  
Aug 14 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.