Query based on values in another table

[FlakyJake]

I am looking to build a query on a number of fields in a amster table based on the "mailing codes" stored in another table.

I want to pull off a list of customers in a master table (122,000) that do not have any of the codes listed in the mailing codes table. What would be the best way to accomplish thi. the tables are not related, the master table is a unix import, fields all over the place and tha mailing codes a straight list of 302 codes.
Hope someone can help. i presume this is a straigthforward query but my mind has gone blank the sql query i was writing became humongous!!!

 

[willir]

FlakyJake

From my perspective, there are two ways of achieving this.
- An SQL query may be possible, but we will need to know more about your data structure. Could you supply us with more info.
- With VBA coding, either DAO or ADO. This approach is slower to run, but more flexible since you can use logic to determine use this field to search, if not found, search the next field, ec.

Richard

 

[FlakyJake]

Data structure - Master table with 60 fields, unix import as below.

0000388,Active,MR,M,abc,5 Wave Drive,,Kansas,Kent,DT6 1ZX,UK,01999
123456,SAME,,Aþ0000388,0105297,Firm,12469,10/08/02,OC,12470,12580,12584þ0000388,Broch
BH98,0010884,05/11/97,11/03/98,OC,MAIL4,,Encl 99,,07/10/98,10/06/99,,99CLIENT,,,Broch
BH99,0035504,04/11/98,10/11/98,OC,99BROC,,,,Encl MS,,04/06/99,04/06/99,,99C,,,,,Broch
BH00,,17/11/99,17/11/99,,97R,,,,,,Encl 01,,31/10/00,31/10/00,,CMAIL3,,,,,,,Encl
02,,20/10/01,20/10/01,,CMAIL3,,,,,,,,Broch
BH02,0096836,20/11/01,30/11/01,OC,2002BROREQ,,,,,,,,,Broch BH03,,21/11/02,21/11/02,,02C


The "þ" delimiter seperating customer data, booking data and mailing data. Also booking and mailings can have multiple references so on import column headings are useless in access as data all over place after customer data.


Already built two tables with those that "have" booked and those that "have not". querying the "haves" I now need to search this table to find all those that have requested brochure without a mailing all those that do not have the "MAIL" tag in fields 30 -60.

Also the "MAIL" tag is made up of over 302 other mail references that really need to be checked. I have stored these in another table "Mailing COdes" to make easier.

Hope this helps