Is it possible to use a wildcard table join in a query?

[RobertIngles]

Good evening everyone!

I need to do a comparative analysis between two tables. Problem is that the fields I need to join don't match exactly. The fields are named "Barcode". The barcode in one table might be XO123456 and I need to match it to the barcode in the second table which would hold the value of simply 123456 without the leading text characters. The leading text is often different ie. XO, YG etc.

Is it possible to create a join that will match the two tables based on the last six digits?

Any and all suggestions are appreaciated!!

 

[PHV]

Use either the LIKE operator or the the Right function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lameid]

Robert,

What you are specifically asking for is called a non-equijoin.

Yes you can do it but you will have to change to SQL view to modify your join. First set up your join as you normally would in the QBE and then modify it to use a comparison using the like operator or right function as PHV suggests.

Another option would be to base a query off of the version that has leading text using the right function. Then use that query in place of that table in the query you are trying to make. You should be able to freely join that way. This may be easier to maintain depending on your comfort with SQL. Regarding execution time, I'm not sure whethr it would prove to be less efficient or not. This may or may not matter depending on your level of patience and size of the table.

 

[MajP]

you could build a simple function like this to return only the numbers

Function getNumbersInText(textIn As Variant) As Variant
  Dim currentPos As Integer
  If Not IsNull(textIn) Then
     For currentPos = 1 To Len(textIn)
        If (IsNumeric(Mid(textIn, currentPos, 1))) = True Then
           getNumbersInText = getNumbersInText & Mid(textIn, currentPos, 1)
         End If
     Next currentPos
  End If
End Function

then in SQL

SELECT 
  tblOne.Barcode, 
  tblTwo.Barcode
FROM 
  tblOne, 
  tblTwo
WHERE 
  getNumbersInText([tblOne].[barcode]))=getNumbersInText([tblTwo].[barcode])

This is not super efficient but will handle lots of cases and both directions
1 is a substring of 2
2 is a substring of 1
any amount of characters or numbers

 

[RobertIngles]

Thanks everyone, I will be trying your suggestions tomorrow and will report back the results.

Thanks so much for your help and also your patience with a VBA and SQL neophyte. I have a book called Learn VBA in Twenty Four Hours by James Foxall which includes a practice module on CD. If anyone can recommend something better and/or suggestions for a good teach your self book/site for SQL and VBA I would greatly appreciate it!

Robert

 

[Andrzejek]

Try

http://www.w3schools.com/sql/

Have fun.

---- Andy