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

Multiple Count Values in Single Query. Is it possible?

jenkinsloveschicken
P: 56
I am somewhat new to Access and have been tasked with creating a reporting database for my operation. The problem I am having is that I am needing to use the Count function to calculate total instances for each phone agent within the report, then use these counts to calculate a percentage for a report.

Sample recordset:

Specialist ID, Specialist Name, Brand Satisifaction, Specialist Satisfaction
111111 John Doe 4 4


Brand and Specialist Satisfaction are a range from 1-5. I am needing to count the number of entries per/Specialist that are >=4 as for Brand and Specialist Satisfaction and a count for total entries for the specialist.

Is it possible to do this within one query? I have no problem getting the values when I calculate each count in a separate query. I know this is probably a simple problem, but I have been trying to figure this out for a while now. Any help is much appreciated.

Gerald
Dec 8 '06 #1
Share this Question
Share on Google+
3 Replies


NeoPa
Expert Mod 15k+
P: 31,661
I am somewhat new to Access and have been tasked with creating a reporting database for my operation. The problem I am having is that I am needing to use the Count function to calculate total instances for each phone agent within the report, then use these counts to calculate a percentage for a report.

Sample recordset:

Specialist ID, Specialist Name, Brand Satisifaction, Specialist Satisfaction
111111 John Doe 4 4


Brand and Specialist Satisfaction are a range from 1-5. I am needing to count the number of entries per/Specialist that are >=4 as for Brand and Specialist Satisfaction and a count for total entries for the specialist.

Is it possible to do this within one query? I have no problem getting the values when I calculate each count in a separate query. I know this is probably a simple problem, but I have been trying to figure this out for a while now. Any help is much appreciated.

Gerald
Expand|Select|Wrap|Line Numbers
  1. SELECT [Specialist ID], [Specialist Name],
  2.   Count([Specialist ID]) AS CountEntries,
  3.   Sum(IIf([Brand Satisfaction]>3,1,0)) AS CountBrand,
  4.   Sum(IIf([Specialist Satisfaction]>3,1,0)) AS CountSpecialist
  5. FROM [YourTable]
  6. GROUP BY [Specialist ID], [Specialist Name]
Dec 8 '06 #2

jenkinsloveschicken
P: 56
Worked like a charm! Thank you for your help!


Gerald
Dec 8 '06 #3

NeoPa
Expert Mod 15k+
P: 31,661
Worked like a charm! Thank you for your help!


Gerald
No problem. Thanks for the feedback. It's always nice to hear back and it shows a question has had an answer for anyone looking later :).
Dec 8 '06 #4

Post your reply

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