Lookup a record from Table A in Table B

[Libre]

Sorry if this is a baby question - I'm largely self-taught and have developed methods to get what I want done. There are a lot of things that I do, though, that may not be the "right" way, the "usual" way, or the most efficient way.
One of those things is how you look up a record in a table.
The situation is the following:
Let's say I have Table A which is a table of ALL products indexed by Part Number, and Table B which is a list of products that have been returned, also indexed by Part Number. I want to determine, for every record in Table A if it has been returned and thus appears in Table B.
What's the best way to do it?
The two ways I can think of are:
1- assign the Part Number that I'm looking for to a variable; loop through every record in Table B and if the record ID matches the variable then I know it's a "hit" and the part number was found;
OR
2- create a single record query that returns only the one record if it finds a match in Table B to the part number I'm looking for from Table A - or no records if it finds no match. It does this by executing a function that is called in the Criteria field in the query builder. The function returns the literal Part Number to the Critera field. Then if I have an empty record set then I know the part number from Table A does not appear in Table B.

I'm not sure if all this is clear. My basic question is:
What's the best/easiest test to see if a record from one table appears in another table?
I don't want to hard code it for that one record, obviously.
I will need to loop through every record in Table A and if it finds a corresponding record in Table B then one thing happens (a flag is set for example that the Part was returned), and if not it will do something else (not set or unset that flag) - so it has to work for any record and all records in Table A.
Sorry if too long winded.
I generally use method 2 above and create single record queries because the loop structure (method 1) is a big pain and I have method 2 down cold - but, it involves creating queries, global variables, and functions - sometimes more than 1 - and I'm not sure that it is the fastest way. If Table A has many records than this step really can slow things down a lot - but so would the loop.

Thanks for any replies.

 

[Brianwarnock]

I'm sure that I must be missing something, but don't you just need to run a query in which you join the two tables together on product number?

Brian

 

[Libre]

Thanks Brian. Yes, I think that gets me most of the way there. I still need a test or a procedure to determine if any particular number from Table A is joined to a number in Table B or if it is null. Thinking about it, the "Unmatched Query Wizard" would also get me very close.

 

[Frothingslosh]

Actually, Brian's right. Just build a query linking the two tables. It'll only show part numbers that exist in both tables.

 

[Libre]

I still need a look-up method to determine if any particular record from Table A exists in the joined query - right?
I have a product number - A12345.
I have a joined query between Table A (all products) and Table B (returns).
Is A12345 in that joined query?
How would you go about testing that?
Again, I could 1- create a procedure with a loop and test, record by record, if any record in the joined query matches A12345. If so then it's a HIT and the product has been returned.
Or I could 2- create a single record query based on the joined query with a function in the criteria field that returns A12345 and then if the single record query is populated with a record then it's a HIT and the product exists in Table B and has been returned.
Pretty much back to my original question.
How would you test to see if any particular record (from Table A) exists in the joined query (Table A/Table B)?
Obviously you could SEE it visually, but I need to do this programatically.

 

[Frothingslosh]

Just go into the query builder and build it.

Link the part number fields.

The entire POINT of an inner join is that only matching records are displayed. In this case, only records that exist in BOTH tables are displayed.

Since you need to do it in VBA, once you have the base query built, copy it into a string variable.

You absolutely don't want to loop; it's ridiculously slow and CPU-intensive. Since you need to look for a specific part, you can add in a WHERE clause on the fly as part of the string. Something like this:

SELECT Returns.Partnumber FROM Returns INNER JOIN Parts ON Parts.PartNumber = Returns.Partnumber WHERE Returns.PartNumber = '" & PartNumberBeingChecked & "';"

(PartNumberBeingChecked is intended to be a string variable. Lose the single quotes in the SQL statement if it's a number, and use a control reference if it's taken from a control instead.)

Then just use CurrentDB.OpenRecordset and check to see if there are any records in that recordset. If there are, the part has been returned.

 

[Libre]

That looks like what I'm trying to do, Frothingslosh, and in fact seems equivalent to my method 2 (which I realize I didn't fully explain or provide code or examples).

 

[Brianwarnock]

Why not two simple update queries, the first turns all flags off , the second sets the flags where there is a match.

Brian

Sorry for the delay in getting back but been out walking, it's a hard full time job this being retired.

 

[Libre]

I'll look into that Brian. I have never used update queries - I always just write VBA procedures and update the data "manually" with the procedure - which is pretty much how I arrived at the method I'm using today. I'll try the update query and see if I can get that to produce what I'm looking for.
Thank you