SQL query with join on substring

S

SriramNA

I have two tables imported into Access.

Sample data:
1. Asset table, containing -
AssetNumber Owner CapitalizationDate ReferenceData
300137015 ABC 23/4/2005 61708
300287721 DEF 22/1/2007 45709
300338450 GHQ 12/7/2009 37154

2. ITEqpt table, containing -
EquipmentID LastUser UsedDate
OKL61708 XYZ 12/4/2012
JA37154 ABC 22/5/2012

and so on.

I need to list all records in the Asset table which have a corresponding
record in the ITEqpt table.

The problem is that the key fields in the two tables are recorded slightly
differently - the ReferenceData in the Assets table typically contains a
substring of the EquipmentID value in the ITEqpt table.

How do I make a report with a join like
ReferenceData = substring in EquipmentID?
 
J

JHB

SriramNA said:
I have two tables imported into Access.

Sample data:
1. Asset table, containing -
AssetNumber Owner CapitalizationDate ReferenceData
300137015 ABC 23/4/2005 61708
300287721 DEF 22/1/2007 45709
300338450 GHQ 12/7/2009 37154

2. ITEqpt table, containing -
EquipmentID LastUser UsedDate
OKL61708 XYZ 12/4/2012
JA37154 ABC 22/5/2012

and so on.

I need to list all records in the Asset table which have a corresponding
record in the ITEqpt table.

The problem is that the key fields in the two tables are recorded slightly
differently - the ReferenceData in the Assets table typically contains a
substring of the EquipmentID value in the ITEqpt table.

How do I make a report with a join like
ReferenceData = substring in EquipmentID?
Click to expand...

Hello,
If the lenght always is 5, then you can use this:
Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))

Jørn
 
S

SriramNA

Um.. that'll catch most of them, but there are many exceptions...

Isn't there a way of simply stripping non-numeric characters from
EquipmentID value?

--
Sriram

"JHB" wrote in message

SriramNA said:
I have two tables imported into Access.

Sample data:
1. Asset table, containing -
AssetNumber Owner CapitalizationDate ReferenceData
300137015 ABC 23/4/2005 61708
300287721 DEF 22/1/2007 45709
300338450 GHQ 12/7/2009 37154

2. ITEqpt table, containing -
EquipmentID LastUser UsedDate
OKL61708 XYZ 12/4/2012
JA37154 ABC 22/5/2012

and so on.

I need to list all records in the Asset table which have a corresponding
record in the ITEqpt table.

The problem is that the key fields in the two tables are recorded slightly
differently - the ReferenceData in the Assets table typically contains a
substring of the EquipmentID value in the ITEqpt table.

How do I make a report with a join like
ReferenceData = substring in EquipmentID?
Click to expand...

Hello,
If the lenght always is 5, then you can use this:
Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))

Jørn
 
B

Bob Barrows

Assuming that the first numeric character is never followed by non-numeric
characters, the Val function will work.
On clng(ReferenceData) = Val(EquipmentID)

If the assumption is incorrect, then you can use 26 nested Replace
functions:

On Replace(Replace(Replace(EquipmentID,"A",""),"B",""),"C","") etc.

or write a function that loops through each character in the EquipmentID,
appending it to a variable if numeric.
 
J

JHB

No, it isn't any easy solution, and the VAL function can't be used because the Val function stops reading the string, at the first character it can't recognize as part of a number.

See this knowledge base article, how you can build a functin you can use in a query;
http://support.microsoft.com/kb/210537

Jørn
 
S

SriramNA

I used the UDF to populate an additional column in the table, which I then
used for the join, worked very well.

Thanks for the help.

--
Sriram

"JHB" wrote in message See this knowledge base article, how you can build a functin you can use in
a query;
http://support.microsoft.com/kb/210537
 
Top