Linking Tables

[AC5FF]

Maybe a better title... Linking tables when fields are not an "EXACT" match...
Will this be possible?

I have two tables, one is an inventory table (INVENTORY) that keeps a running total of each stock number's inventory. A second table defines (PARTS LIST) what stock numbers are required per individual assembly. These two tables are linked together using the stock number field.

Where I am having problems:
In the PARTS LIST table I will have a Stock number of 1234-12-1234 listed.
In the INVENTORY table, I could possibly (not all the time) have a stock numbers of 1234-12-1234, 1234-12-1234-S1, 1234-12-1234-S2, etc...
{Note: The stock number in the PARTS LIST table could also be one of the -S stock numbers - but still require a sum of all the similar stock numbers....}

When I run my query if I have zero inventory of 1234-12-1234 I get a zero; but I need it to add together all of the stock numbers (with the -S). For the life of me I have not been able to work this out. Shouldn't I just be able to use a wild card within the query?

Here's the SQL:

PARAMETERS [PL - 1] Text ( 255 ), [PL - 2] Text ( 255 ), [PL - 3] Text ( 255 ), [PL - 4] Text ( 255 ), [PL - 5] Text ( 255 ), [PL - 6] Text ( 255 ), [PL - 7] Text ( 255 ), [PL - 8] Text ( 255 ), [PL - 9] Text ( 255 ), [PL - 10] Text ( 255 );
SELECT [PARTS LIST - ALL].*, [INVENTORY - ON HAND - TOTALS ONLY].[ON Hand]
FROM [INVENTORY - ON HAND - TOTALS ONLY] RIGHT JOIN [PARTS LIST - ALL] ON [INVENTORY - ON HAND - TOTALS ONLY].[STOCK NUMBER] = [PARTS LIST - ALL].[STOCK NUMBER]
WHERE ((([PARTS LIST - ALL].PL)=[PL - 1] Or ([PARTS LIST - ALL].PL)=[PL - 2] Or ([PARTS LIST - ALL].PL)=[PL - 3] Or ([PARTS LIST - ALL].PL)=[PL - 4] Or ([PARTS LIST - ALL].PL)=[PL - 5] Or ([PARTS LIST - ALL].PL)=[PL - 6] Or ([PARTS LIST - ALL].PL)=[PL - 7] Or ([PARTS LIST - ALL].PL)=[PL - 8] Or ([PARTS LIST - ALL].PL)=[PL - 9] Or ([PARTS LIST - ALL].PL)=[PL - 10]))
ORDER BY [INVENTORY - ON HAND - TOTALS ONLY].[STOCK NUMBER];

THANKS!

 

[AC5FF]

I should probably add - I'm working in Design View for creating my queries; I've not quite graduated to writing them in SQL.. I've got the books, and I practice, but I have not gotten proficient yet for writing complex queries ....

 

[pbaldy]

One option would be to create a query that returned records from the Inventory table but added a calculated field of Left(FieldName, x) that would return a value that could be joined to the other table. Then use that query in this query instead of the actual table, joining on that calculated field.

An option that would force you into SQL view would be a non-equi join, like:

...RIGHT JOIN [PARTS LIST - ALL] ON Left([INVENTORY - ON HAND - TOTALS ONLY].[STOCK NUMBER], x) = [PARTS LIST - ALL].[STOCK NUMBER]