Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 8th, 2004, 05:10 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stuck with Select Query...

I have a table that contains like the following:

EmployeeName, Reason

I need to count the occurences of each reason for each employee and select the reason that occurs the maximum number of times.
I thought about doing a nested Select query, but I can only get this to return the employee with their reason that occured the maximum number of times

ie
Joe Bloggs, Credit Authorised

I can't seem to get the query to return each employee with their reason that occurred for them the maximum number of times.

ie
A N Other, Enquiry
Joe Bloggs, Credit Authorised
John Doe, Escalated Call
S O Else, Credit Authorised

Thanks in advance
Ben
  #2 (permalink)  
Old April 8th, 2004, 09:10 AM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Create a query of Counts and name it qryEmployeeReasonCount

SELECT EmployeeName, Count(Reason) AS CountReason
FROM tblEmployeeReason
GROUP BY [EmployeeName & ' ' & Reason, EmployeeName
ORDER BY EmployeeName & ' ' & Reason;

Then take the max from that query by writing a new query referencing that query:

SELECT EmployeeName, Max(CountReason) AS MaxCountReason
FROM qryEmployeeReasonCount
GROUP BY EmployeeName;

This isn't finished as I didn't take the time to pull the reason but should get you pointed in one workable dirction.

Ciao
Jürgen Welz
Edmonton AB Canada
jwelz@hotmail.com


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Query gregalb SQL Server 2000 2 November 15th, 2007 10:56 PM
SELECT from SELECT query? seananderson Access 1 October 12th, 2007 12:40 AM
Need help on select query arul1984 SQL Server 2000 2 July 4th, 2007 01:49 AM
Select Query Help dbartelt Access 1 June 13th, 2005 08:06 AM
select query collie SQL Server 2000 2 January 17th, 2005 03:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.