Access query help

[rkumarchennai]

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:
http://oi61.tinypic.com/2dbmlaw.jpg

Output needed:
http://oi61.tinypic.com/2ex3apv.jpg

Rajesh

MS-Access query.zip

[John_G]

Hi -

You are going to need a second query, a summation query which counts how many there are of each Grade+Designation combination. Now, in a new query, link that summation query to your main table on Grade and Designation. You can then use an IIF function to display the result you want.

Give me a while and I'll see if I can illustrate it for you.

HTH

John

[John_G]

Hi -

Here you are - images of the query designs and the results:

http://i59.tinypic.com/2hda53s.jpg summation query design
http://i59.tinypic.com/sphvzb.jpg summation query results
http://i59.tinypic.com/2vvn81d.jpg listing query design
http://i58.tinypic.com/2nhjajr.jpg listing query results


The full text of the IIF expression for List title is:

List title: IIf(designationcounts.countofusername>=10,Designat ion.Designation,"Misc - " & Designation.grade)

John

PS - Thank you for showing me tinypic.com - I was not aware of it - very useful.