Originally Posted by
smithse
If, and only if the part number base is always 8 characters, then you could do the following:
You could break it down into 2 queries. From what I have just done, I have 495 matches. Does this sound right?
Create 2 queries, the first named: qryPNWithBase and containing the following SQL statement:
SELECT [Table A].[Part Number] AS PartNumber, Left([Part Number],8) AS PNBase
FROM [Table A];
Create a 2nd query which is joined to the results of the first. You can name this query anything you like. It's SQL statement is:
SELECT [Table B].[Part Number] AS PNBase, qryPNWithBase.PartNumber AS PNFull
FROM [Table B] INNER JOIN qryPNWithBase ON [Table B].[Part Number] = qryPNWithBase.PNBase;
The first query creates a column alongside the full length part number (if thats what you call it), and we then use this to join the part number bases in table b.