Join based on part of one field

[JStreet]

I have 2 Tables.
One has a list of our part numbers and the other is from our supplier.
The difference in the part numbers is that they have started to put a 56 in
front of them and we have not changed all of our numbers over.
Is there away I can match on the numbers by telling it to look at what comes
after the 56 prefix on the supplier table?
But will this work for any of the numbers that we have put the 56 in front
of ours already?
Unfortunately I wish I could say that all the numbers are a standard length
but they aren't.

 

[Marshall Barton]

I have 2 Tables.
One has a list of our part numbers and the other is from our supplier.
The difference in the part numbers is that they have started to put a 56 in
front of them and we have not changed all of our numbers over.
Is there away I can match on the numbers by telling it to look at what comes
after the 56 prefix on the supplier table?
But will this work for any of the numbers that we have put the 56 in front
of ours already?
Unfortunately I wish I could say that all the numbers are a standard length
but they aren't.

Try a cirteria along these lines:

=partnum OR =Mid(partnum, 3)

 

[John W. Vinson]

I have 2 Tables.
One has a list of our part numbers and the other is from our supplier.
The difference in the part numbers is that they have started to put a 56 in
front of them and we have not changed all of our numbers over.
Is there away I can match on the numbers by telling it to look at what comes
after the 56 prefix on the supplier table?
But will this work for any of the numbers that we have put the 56 in front
of ours already?
Unfortunately I wish I could say that all the numbers are a standard length
but they aren't.

Could there be a 56 in one of your old part numbers? Please post some examples
of the old and new part numbers. You may have a real ambiguity here!

Try a Join like

SELECT yourtable.*, suppliertable.*
FROM yourtable
INNER JOIN suppliertable
ON yourtable.partnumber = suppliertable.partnumber
OR "56" & yourtable.partnumber = suppliertable.partnumber;

This probably won't be updateable and won't let you use the design grid but
should at least find the matches.

John W. Vinson [MVP]

 

[JStreet]

Some of our numbers have the 56 in front but other don't so I was wanting to
basically ignore the first part and look at the tail end of these two fields
and find the matches that way.
Right now I know that perfect match is 449 and the ones that actually match
equal 247.
Exactly how many places I will need to use -- I will just have to keep
trying to see when I get the closest to matching the 449 records.

 

[JStreet]

Can you explain what the Mid and the ,3 means exactly?

I get no records matching when I use your suggestion and replace the partnum
with the name of my field

 

[John Spencer]

Perhaps you can join using the like operator instead of the equals operator.
This type of join can only be done in the SQL window and cannot be displayed
in the query grid (design view).

SELECT SupplierList.PartNo, YourList.PartNo
FROM SupplierList INNER JOIN YourList
ON SupplierList.PartNo LIKE "*" & YourList.PartNo

This could give false matches in cases where your part no is 53 and the
supplier list has numbers like 562353 and 5653.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Searching VBA Help for names you are not familiar with can
save at lot of time posting back and forth.

Mid(s,n) is a function that returns the remainder of string
s starting with the nth character.

Do already have part numbers in both tables with the 56 in
front?

Are you trying to search one of the tables for a part number
where the criterial does not have the 56. If so, what I
posted earlier should work.

If you are trying to find all records in both tables that
match a record in the other table, then you need to avoid
the query design window and work in SQL view to create the
query. It might look something like:

SELECT t1.*, t2.*
FROM t1 INNER JOIN t2
ON t1.partnum = t2.partnum
OR (t1.partnum = Mid(t2.partnum, 3)
AND Left(t2.partnum,2) = 56)
OR (t2.partnum = Mid(t1.partnum, 3)
AND Left(t1.partnum,2) = 56)