Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
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 June 15th, 2004, 06:57 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default Select companies by title ranking

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
 
Old June 15th, 2004, 10:49 PM
Authorized User
 
Join Date: May 2004
Posts: 52
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 17th, 2004, 08:07 PM
sal sal is offline
Friend of Wrox
 
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old June 17th, 2004, 10:15 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old June 18th, 2004, 11:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 149
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to improve website ranking in Google Rajesh225 .NET Framework 1.x 3 June 28th, 2007 07:30 PM
Ranking in a Group paul20091968 Excel VBA 8 November 2nd, 2006 09:45 AM
Making a game ranking LiamUk PHP How-To 4 July 30th, 2004 06:21 AM
Name or Title charlie3 VBScript 2 July 8th, 2003 05:00 PM





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