Wrox Programmer Forums
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
Old April 16th, 2014, 06:26 AM
Registered User
Join Date: Apr 2014
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Access query help

I have a requirement to categorize a field in the employee database.

The requirement is: for any given user designation, if the total number is 10 or more, I need the actual designation itself as the output. However, if a particular designation in the organization is less than 10 in total, the output should be 'Misc - and the grade'.

Also, this will need to be done for each grade as well. For example, though the staff designation 'Software Engineer' count is 15, the designation is present across two grades A and B. So, in the output, for the staff with Grade A will show the actual designation as the count is 11, and for Grade B staff with the desgination 'Software Engineer', since the count is 4, it should display the output as 'Misc - Grade B'.

I would like to achieve this in MS Access 2010. Would highly appreciate if you could help.

Source data:

Output needed:
Old May 7th, 2014, 09:18 PM
Authorized User
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts

Hi Rajesh,

Do you still need help with this?

You need a subquery to create the record counts with the key Designation/Grade. Use this to determine whether "Misc - " & Grade or Designation is returned. You may be able to use the DCount() function.

If you still want help, I'll set up the data and post the SQL required, no point doing all that if you've already solved your problem though.

Old May 7th, 2014, 09:58 PM
Authorized User
Join Date: Oct 2010
Posts: 64
Thanks: 0
Thanked 16 Times in 16 Posts

Hi again,

Here's an example using DCount, a subquery may be better, but I expect the number of records to be queried is never going to be huge, so this should be quick enough.

SELECT Positions.Username, Positions.Grade, Positions.Designation, IIf(DCount("Designation","Positions","Grade = '" & [Grade] & "' AND Designation = '" & [Designation] & "'")>9,[Designation],"Misc - Grade " & [Grade]) AS [Output Required]
FROM Positions;
I've assumed your table is called Positions, so change accordingly. I only used the grade, that is A, B, C and D in the Grade column as there's no need to store the word Grade in every record, it's inefficient. You should also consider having another table with Designations stored with a unique ID and use that as a foreign key in your Positions table.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Update query doesnt run when Select query does (In MS Access) rmccafferty SQL Language 3 February 11th, 2010 04:54 AM
Access query SheriV Access VBA 3 September 15th, 2009 10:29 AM
Access Query gre_smi SQL Server 2000 2 January 11th, 2006 08:59 AM
need access query kale_tushar Access 2 January 3rd, 2005 05:26 AM
Access: need a Query. please help me... alienscript Access VBA 1 December 14th, 2003 01:02 PM

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