Prioritse a queries return value

lawtonl

Registered User.
Local time
Today, 01:43
Joined
Nov 3, 2009
Messages
28
Hello,

I'm not sure how easy or if indeed it is possible but I have a query that is pulling through values for contacts. I have two tables Accounts and Contacts, for some reason or another the values in contacts have duplicates of the same contact but a seperate job titles. See below....

0018544 DarthVader MD
0018544 DarthVader Employee
0018544 DarthVader Store Assistant
0018559 MARKSWALLOWS FD
0018559 MARKSWALLOWS MD
0018636 Johnny Cash Employee
0018636 Johnny Cash Employee

I would like to if possible from that return

1st MD
2nd FD
3rd Employee
4th Store assistant

Is this possible?

Any help is appreciated.

Thanks

L
 
From the record list you provided, you could use two queries to get what you require.

1. select the records into a temp table with an autonumber field ordered by their account number, then your priority list. You can't simply use the job title field because this will order the records alphabetically, and not the order you want. You'll need to use a nested iif function to achieve this, or maybe it'd be easier to add an extra column with the priority (ie. 1 for MD, 2 for FD and so on) then order by this.

2. create a query to select the minimum unique rec number (the autonumber field) from the tmp table - this will be the record with the highest priority grouping on the account number.
 
That's an excellent solution!

Thanks
 

Users who are viewing this thread

Back
Top Bottom