Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

SQL query with join on substring

217 views
Skip to first unread message

SriramNA

unread,
Jun 23, 2012, 3:39:49 AM6/23/12
to
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?

--
Sriram

JHB

unread,
Jun 23, 2012, 5:58:11 AM6/23/12
to

"SriramNA" <M8R-a...@mailinator.com> skrev i en meddelelse news:js3rs4$35c$1...@dont-email.me...
Hello,
If the lenght always is 5, then you can use this:
Assettable.ReferenceData = clng(right(ITEqpttable.EquipmentID,5))

Jørn


SriramNA

unread,
Jun 23, 2012, 8:22:59 AM6/23/12
to
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 news:4fe5933a$0$289$1472...@news.sunsite.dk...

Bob Barrows

unread,
Jun 23, 2012, 9:28:34 AM6/23/12
to
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.



SriramNA wrote:
> 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?
>
>

JHB

unread,
Jun 23, 2012, 11:37:36 AM6/23/12
to
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


"SriramNA" <M8R-a...@mailinator.com> skrev i en meddelelse news:js4cf4$o7f$1...@dont-email.me...

SriramNA

unread,
Jun 25, 2012, 12:40:38 PM6/25/12
to
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 news:4fe5e2c7$0$284$1472...@news.sunsite.dk...
0 new messages