Linking tables with a partial field

[Gkirkup]

I have two tables. One table contains partial part numbers, and the other table contains complete part numbers. For instance, the partial might be 'E123' and the complete might be 'E123-A1' and 'E123-A9'.
What I want to do is to see all of the complete part numbers that contain the partial part number. I do this in a query, using LIKE [Partial] & "*".
That works, sort of, but there are several hundred partial records and it is very slow with lots of 'not responding', sometimes for several minutes.
The two tables are not linked, because the partial part number in one table will not link directly with the full part number in the other table.
I looked at the SQL and tried creating a link between the tables using PARTIAL = LEFT(PARTNUMBER, LEN(PARTIAL)). This ran for a while (a third of the Run Query bar) and then hung.
Is there any way that I can link the tables if the fields are not the same length? My thought was that if I can set up that link, then LIKE [PARTIAL] & "*" will work properly.

Robert

 

[Uncle Gizmo]

In the table that contains the complete number, do you have the ability to add an extra field? If you do I would suggest that you divide the part number into the repeating part and the extra part in to two separate Fields.

 

[arnelgp]

how about using Between in your criteria:

Where PartNumber Between ([Partial] & "-A") And ([Partial] & "-z")

 

[jdraw]

Robert,

Can you describe to us what exactly is the purpose of the table with partial part numbers?

 

[MarlaC]

The easiest way to handle this is to create a query on the table with the full part numbers and add a field to the query which calculates only the partial part number. Then replace the table that only has the full part numbers in your query with the query you've just built and link on your new field.

 

[gblack]

One issue I see here is that you aren't really telling us what you mean by partial. If there's always a dash separating the large number, then (in order to get the partial) you could do something like: Mid([PARTNUMBER], 1, Instr([PARTNUMBER], "-") - 1) to give you everything up to the dash (i.e. "-"). If there is not order to your partial number and you could have:
'E123' or '12' or '23' or 'A9' and then the complete field might be 'E123-A1' and 'E123-A9'.
Then you have an issue... if not, you could use the above method submitted by MarlaC to link those values...

using the a string function like I posted to: [add a field to the query which calculates only the partial part number]

-GL

 

[Galaxiom]

I do this in a query, using LIKE [Partial] & "*".
That works, sort of, but there are several hundred partial records and it is very slow with lots of 'not responding', sometimes for several minutes.

Have you got indexes on the fields in the table?

 

[Gkirkup]

Some good suggestions. The format of the partial / complete part numbers I gave was just an example. The actual part numbers can have any combination of letters and numbers. 2 to 40 characters. So sometimes the 'partial' part number is the whole part number. Sometimes it is just a few letters and numbers, and there are several variants of the suffix in the 'full' part numbers. I need all of those variants.

Robert

 

[MarlaC]

Some good suggestions. The format of the partial / complete part numbers I gave was just an example. The actual part numbers can have any combination of letters and numbers. 2 to 40 characters. So sometimes the 'partial' part number is the whole part number. Sometimes it is just a few letters and numbers, and there are several variants of the suffix in the 'full' part numbers. I need all of those variants.

Robert

I'm afraid realistically that means adding a table field to contain what you need to link on.