matching similar numbers, but not the same numbers

Hi all. I hope that I can explain this problem clearly so that I may be understood, so please bear with me.

I am trying to build a query that will match numbers from one table to numbers from another table with an extension added. At my company we must generate a report each week for one of our work sites that shows part numbers with inventory levels lower than the minimum, letting the purchasing people know that part number needs to be ordered. Our problem occurs because out of the 32,000 part numbers on file at this site, we have up to THREE part numbers that are for the same part.

Each number is an identical 7-digits for each part, with an added extention showing that part's origin. An example would be: 7449836 (base part number) 7449836-C (company owned part number) 7449836-ISA (third party part number). As we are trying to liquidate the stock from the -C and -ISA parts numbers, we need to know if when the stock gets low on the base number parts, there is a corresponding part number in the other two categories that has sufficient stock to sell.

I have started this query process by creating two make table queries. The first one is a simple conversion of the purchase report query we use to show low stock numbers into a make table. I have added a field in the original query that generates the part number's first seven digits.

(NEW PROD #: Left([PRODUCT #],7))

I did the same thing for our inventory file, so that I would have a matching number to join each table to. I have tried left statements such as:

LIKE LEFT([PRODUCT #],7)&"-C"

I have also used IIF statements with the same criteria, with no positive results.

I have also tried creating four make table queries, breaking down the part numbers. The first is for the purchase report, the second for all '-C' parts, the third for all '-ISA' parts, and the fourth for all base number parts. In each make table query I added a column using this example:

NEW PROD #: Left([PRODUCT #],7)

This gave each table a common field to link to. I linked all the tables to the purchase report table, using the join property showing all records from the purchase report table and all matching records from the other tables. In the query, I added a column for each table's data to show the corresponding part number records that were the same as the purchase report's records using this example:

ISA PRODUCT #: IIf([PURCHASE REPORT TABLE 3]![PRODUCT #]=[PURCHASE REPORT TABLE]![PRODUCT #],Null,[PURCHASE REPORT TABLE 3]![PRODUCT #])

The only snag I have encountered with this approach is for a part number section that has a possible TWO extensions instead of one. These are for metal stamps, labeled A-Z and 0-9. An example of this part number would be 7894567-Q-ISA, which would indicate this is a stamp for the letter 'Q' from the third party vendor. The query in its current form is jumbling the stamp records, but accurately sorting the other data.

Does anyone know how I could possibly work around this?

I need some suggestions as to a new approach to my logic, because I am running out of ideas fast!

Any and all help is greatly appreciated!

Thanks!