4

I have two tables a and b in my Access Database. In both tables I have the field ID. However in table a my ID field is prefixed with '31' where as my ID field in table b is not.

So for example

table a                 table b

ID      field2          ID     field3
31L123  test123         L123   123test
31L122  test321         L122   321test

My prefixed field table is imported regularly from an Excel export, I understand I could remove the prefix at the excel level but is there a way to join the two tables on the ID field by using some sort of Concatenate function on the join within the SQL statement by any chance?

So for example something along the lines of:

SELECT Id, Field2, Field3 
FROM a LEFT JOIN b ON CONCATENATE('31', a.ID) = b.ID
WHERE a.Field2 = 13

I am not sure if this is the correct approach or not - and that is why I can not seem to find any existing help on my problem (ignoring processing the fields at the excel level before the import).

ppw
  • 155
  • 4
  • 17

3 Answers3

4

CONCATENATE() is not supported in Access SQL. Generally you would use & for concatenation.

However I don't think you need concatenate anything for your join's ON condition. Just use Mid() to ignore the first 2 characters ...

ON Mid(a.ID, 3) = b.ID

That should work, but performance may become unacceptable as the tables grow over time. You should get better performance by "cleaning" the a.ID values immediately after each import ...

UPDATE a
SET ID = Mid(ID, 3)
WHERE ID ALike '31%';

Then your SELECT query's join can use a simpler, faster ON condition ...

ON a.ID = b.ID

Ensure ID is indexed to allow optimal performance.

HansUp
  • 95,961
  • 11
  • 77
  • 135
1

Why don't you just update your query to be something like:

SELECT Id, Field2, Field3 
FROM a LEFT JOIN b ON RIGHT(a.ID,4) = b.ID
WHERE a.Field2 = 13

You could also take the a.ID, and subtract 2 digits from the length of a.ID and then use the RIGHT-most characters in the event of a.ID being longer than 6 characters wide. And, re-writing, we get:

SELECT Id, Field2, Field3 
FROM a LEFT JOIN b ON RIGHT(a.ID,LEN(a.ID)-2) = b.ID
WHERE a.Field2 = 13

Hope this helps?

Leptonator
  • 3,379
  • 2
  • 38
  • 51
  • You assume that ID will always be L and 3 digits – sagi Mar 17 '16 at 14:52
  • 1
    That is why I also suggested the subtraction of the 2 digits (31) from the string and use the right-most characters.. – Leptonator Mar 17 '16 at 14:56
  • Thank you for your comprehensive answer @Leptonator Though I did not use this method to solve my problem for this specificly, I will surely make use of your answer in the future. – ppw Mar 18 '16 at 08:42
0

I would just add the "31" using MS Access's string concatenation:

SELECT Id, Field2, Field3 
FROM a LEFT JOIN
     b
     ON ("31" & a.ID) = b.ID
WHERE a.Field2 = 13;

Some databases would still use an index on b.ID for this query.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786