I have a table name tech_id contains field CORP,Contractor. There is another table name LastValidTech contains four fields, Account1, Account2, Account3, Account4. Table structure is first 4 digits of LastValidTech (Account1,Account2,Account3,Account4) is CORP.

tech_id
CORP,CONTRACTOR
1211,ABC
1222,EFG
1333,KKL

LastValidTech
TicketNo, Account1, Account2, Account3, Account4
1122,01211-12345-01
1123, ,01222-32456-93
1124, 01333-73737-04

Some times the Account1 has a value, Some times Account2 in another record has a value and same some times Account3 and Account4 has a value. How to i join both tables in a sense that if Account1 has a value and match with first 4 digits of CORP then it will connect to CORP or if Account2 has a value and match with first 4 digit with CORP it will connect to CORP or if Account3 and Account4 has a value and match with first 4 digit with CORP then it will connect to CORP.

Thanks.

Hi

Your design sounds iffy to say the least. Are you familair with normalisation and relational database design?

http://r937.com/relational.html
http://www.datamodel.org/NormalizationRules.html

Have a read of the above. A normalised design and a rethink of your join columns could make this a trivial query.

HTH

pootle flump ur codings are working excelent.

A more enlightened why of putting it is your second table LastValidTech should be structured with only one account field

TicketNo, CORP, Account

Separate the Account into CORP into in constituent parts

If there were 4 (old) Accounts values relating to the Ticket there would be 4 records if there was one there would ne one record.