Query two tables by "Like"

[AtLarge]

This seems so easy and yet I am stumped with where to start. Two tables:

Table 1 is a list of part numbers in this format 123-456-789

Table 2 is a list of part number prefixes that identify what the part number family is.

Example: part number 123-456-789 is a "widget" because in Table 2 is the field 123 and it's record is "widget".

So, how do I query Table 1 so that it draws in the LIKE fields from Table 2 based on the matching prefix NOT the whole part number?

Query output would look like this:

Table 1....................Table 2.....................Table 2
PartNumber.............. Prefix.......................Prefix Description
123-456-789............123..........................Widget

I tried to go the other way and query Table 2 but I could not get the expression to do a LIKE on Table 1 by matching just the first three characters. Any suggestions would be appreciated. TIA.

 

[Brianwarnock]

Try

Where table2.prefix=left(table1.partnumber,3)


Brian

 

[AtLarge]

Well, it kinda worked Brian. Here is what I used.

SELECT Table1.IPN, Table2.Type
FROM Table1, Table2
WHERE Table2.PN_Prefix=Left(Table1.IPN,3)

I have to tinker with it a little more to find out why not all the records are picking up but it's very close.

You da Man!

 

[Brianwarnock]

The only reason I can think of for it not working correctly is if not all of the prefix are 3 characters.

Brian

 

[AtLarge]

Turns out I had some part numbers in Table1 with a prefix that wasn't in my prefix Table2 list. Oops. All is well now.

 

[Brianwarnock]

Great, thanks for coming back on that so that we know its resolved.

Brian