I have a table that contains Company information and the employees who work there and their titles.

I need to make mailing lables based on the rankings of thier titles but only one per company.

My preference is in this order:

1. General Manager titleID number =460
2. President titleID number =360
3. Vice President titleID number =390
4. Production Manager titleID number =520
(there are more titles in the table, but these are the only ones I want)

So, if a company has a General Manager select him and don't select anyone else from his company, BUT if there is no General Manager then check to see if there is a President if so, use him, BUT if none then is there a Vice President if so use him BUT if there is no Vice Prsident is there a Production Manager if so use him and if there is no Production Manager then don't use anyone else from that company.

I have 1800 names for 600 distinct companies, so at most I only want to sent out 600 letters (there will be less since some companies will have none of the above).

I can't seem to do a ranking or sorting or filtering of any sort that works. I thought I could do a make table for each title then delete from the master table starting with 1 and going to 4 but everytime I ran the delete querey it said I could not delete.

So any idea on how I could get out a letter to each company but only to one and only one of the 4 titles I specified?

__________________
Mitch

No real code here right now, just a quick and dirty concept to make the code.
=======================================

Make a loop to do the following for each company:

Retrieve the following records for each company. Make a string of IF's checking for
IF Select titleID number =460 then tag to send letter, else
IF Select titleID number =360 then tag to send letter, else
IF Select titleID number =390 then tag to send letter, else
IF Select titleID number =520 then tag to send letter, else
No one in this company qualifies, end loop, go to next company till all companies are done.


The "tagged" records could be appended to a temporary table for label printing, vs. just "tagging" in some manner. Sanity check would be 6107 records in temp table for 6107 companies.

This should be all in your table structure. You have contacts(table) and companies(table) and titles(table). Yoy select a company and a title for your contact table and you group by company and order by title (or something like that) and you put a number in your title table and select max to mail the letter to(max being the highest level of head title). This woul also work for more than just 4 titles.

I dislike loops.



Sal

Maybe i am making this more difficult then needed, but I don't see how I generate a listing of just one receipient per company, that one being the one with the highest title ranking.

I got it to work! :D

I had to make 4 queies:

1. Ranks
2. Max of Ranks
3. Top Ranking Employee at each Company
4. Top Ranking Employee at each Company for ALL companies

I could not find a way to do it in less, but there probably is.

=========================
Thanks for the time and trouble,
Mitch