Query to match records in two tables 1

[Brianfree]

Hello, i have table in Access 2013 (Enquiry) to which i need to resolve an OEM against in another table. Some of the Enquiry data might already be in the correct format but other rows might not. I need some code that when i select a button on a form it automatically looks up the OEM to see if it is correct in OEM. If not then it sees it if exists in Interchange1 or Interchange2. If it does then it returns the OEM.

Please can anyone advise??

Please see examples below...

Enquiry, QTY
-------
abc123, 10
zzz-abc456, 50
zzz-abc789, 40
abc-234, 22
abc345, 1

OEM, Interchange1, Interchange2
-------------------------
zzz-abc123, abc123, abc-123
zzz-abc234, abc234, abc-234
zzz-abc345, abc345, abc-345
zzz-abc456, abc456, abd-456

Output, QTY
------
zzz-abc123, 10
zzz-abc456, 50
zzz-abc789, 40
zzz-abc234, 22
zzz-abc345, 1

Kindest thanks,

Brian

 

[dhookom]

I would probably do this in two queries. The first would normalize the OEM table:

SELECT OEM as EnqOEM, OEM  
FROM OEM
UNION ALL
SELECT Interchange1, OEM  
FROM OEM
UNION ALL
SELECT Interchange2, OEM  
FROM OEM

You can then join the union query to Enquiry on Enquiry = EnqOEM.

Duane
Hook'D on Access
MS Access MVP

 

[Brianfree]

Hello dhookom, thankyou for your fast reply. I have tried this and the result just shows two columns that are identical (have the OEM) Should i see the interchange1 and intercgange2 in the same column?

Many thanks

Brian

 

[dhookom]

If your data was accurate, the query I provided should display records like:

[pre]
EnqOEM OEM
zzz-abc123 zzz-abc123
zzz-abc234 zzz-abc234
zzz-abc345 zzz-abc345
zzz-abc456 zzz-abc456
abc123 zzz-abc123
abc234 zzz-abc234
abc345 zzz-abc345
abc456 zzz-abc456
abc-123 zzz-abc123
abc-234 zzz-abc234
abc-345 zzz-abc345
abd-456 zzz-abc456[/pre]

Share your SQL view of your query(s).

Duane
Hook'D on Access
MS Access MVP

 

[Brianfree]

Hi, dhookom got it working - some of the data had spaces at the end so just used a trim function - works a treat!!

Thanks for your help.

Many thanks,

Brian