Linked Tables with Wildcards?

[AC5FF]

Maybe my brain took a vacation today; I thought this would be simple but I can't get anything to work correctly. Hopefully it's an easy oversight on my end.

Is it possible to link the two tables using a wild card?

For example, my Parts List table will have a stock number X
But my Master Stock Table will have the stock number X and X-1 and X-2, etc.

I need to be able to pull out all the "X"s from the master stock table based upon a variable set from the Parts List table.

Here's the SQL for what I have so far...

 

[AC5FF]

Wow - I was totally brain fried yesterday. No wonder no one answered this thread...

Let me see if I can clarify this all a little...

Here is an example of two tables. The two columns on the left is sample data from a Parts List table. The three on the right is sample data out of the Inventory Master table.

PL	PL Stock Number		Inv Stock Number Manufacturer Part Number
0810-1	[1550-02-0021]		3135-01-0012-S1	     B2	T491B106M010AS
0810-1	3135-01-0012		3135-01-0013-S4	     B1	CC504N180J-RC
0810-1	3135-01-0013		3136-01-0009-S3      B3        C0603C475K8PAC
0810-1	3136-01-0009		4850-01-0017-S2	     C3	C0603C104K8RAC
0810-1	4850-01-0017		5150-04-0001	     B2	C0603C105K4PAC
0810-1	5150-04-0001		5150-04-0001-S1	     E2	PCB-0810-1
0811-0	3135-01-0013		3135-01-0012-S2	     E1	ABM7-16.000MHZ-D2Y-T
0811-0	3135-01-0012		3135-01-0012-S3      F3         MCP73831T-2ACI/OT
0811-0	5150-04-0001		3135-01-0012	     F2         TPS76933DBV
0811-0	5150-04-0001		1550-02-0021	     F1	 MC9S08JM60CQH
0816-8	3135-01-0012		3135-01-0012	     A4	 XB24-BCIT-004
0816-8	5150-04-0001		3135-01-0013	     A3	 WP3VEGW
0816-8	3135-01-0013		3136-01-0009	     A2	 TSOP36156
0816-8	5150-04-0001		4850-01-0017	     A1	 LIR2430-PDY2

The two tables are joined on the Stock Number field.
For my query I am wanting to pull all the data from the parts list table where PL=0815-1 along with the Manufacturer and Part Number information from the Inventory Master Table. Straight across, that's easy.
However, for example, the Parts List table has a Stock Number of 3136-01-0009 and I want to pull that line from the Inventory Master Table, as well as the 3136-01-0009-S3 information.

Everything I have tried (i.e. wildcards in where statements) has not worked.
Any suggestions on what I can do to make this happen?

Thx!

 

[Brianwarnock]

Have you tried using Left in the Where?

Where  [PL Stock Number] =  Left([Inv Stock Number],12)

Brian

 

[AC5FF]

Brian;

Thanks - This did help me out a bit... But I think it's limiting me now in the 'where' clause.
Or I could be wrong.

I think I need a wild card here someplace. I went about this a different way after rethinking for a few days. I wrote a query that will just give me the basic stock number out of the PL table. I.E. if the stock number has brackets around it - [XX] or ends with a -Sx, I remove all of that so I am looking at just a raw stock number of 12 digits. It took a little playing around with RIGHT/LEFT/MID statements in an IIF line, but it works...

So I now have that query linked with my Inventory Master and I only get results where the stock numbers are the same.

What I need to be able to grab is - see data example above - if my PL table calls for a stock number of 3135-01-0012 I want to be able to list all the stock numbers that begin with that (i.e. would be exact match or have a -Sx following it)...

Thx