INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Query to match records in two tables

Query to match records in two tables

(OP)
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...

CODE

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

CODE

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

CODE

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


Kindest thanks,

Brian

RE: Query to match records in two tables

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

CODE --> sql

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

RE: Query to match records in two tables

(OP)
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

RE: Query to match records in two tables

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

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 

Share your SQL view of your query(s).

Duane
Hook'D on Access
MS Access MVP

RE: Query to match records in two tables

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close