Query 2 tables where the search string is "contained" in rather than only "equals"? (1 Viewer)

[Hanna]

Hi, I'm trying to tidy up some data and ... as you may well spot(!) I'm pretty new to Access.

I've got 1 table [called old] which has 2 fields: SKU_old and the associated drawing numbers: drawing_ref
And the other table [called new] which 1 field: SKU_new
I can see how to set up the relationship so that the there is a one to many relationship between the SKU_old and SKU_new from the different tables.

And I have managed to set up a query which will cross reference which records from SKU_new are also in the old table & show the corresponding drawing numbers.

BUT.... (and here's the but haha!)

I would like to do a query as above but rather than where the SKU_old EQUALS the SKU_new, I'd like to be able to find records which CONTAIN or maybe even BEGIN or ENDS WITH

Excuse the capitals, I've been looking into boolean expressions to see if this would help lol

Hope this may help:

OLD TABLE

id	SKU_old	drawing_ref
1	ABC123	54-LP1682
2	ABC123	HMPP-14
3	ABC123	9999424

NEW TABLE

ID	SKU_new
1	ABC123
2	BC12
3	ABC12

At the moment, the only way that I can query this is where the SKU_old on the new table *equals* the SKU_new on the new table. As per Line ID 1 on the new table.

But is it also possible to design the query so that access will generate results for:
New Line ID 2: where the SKU_new is *contained* in the SKU_old? i.e. BC12 is contained in ABC123 & show the drawing numbers?
New Line ID 3: same except for the ABC12 "begins with"

So in an ideal world, the results of my query would look something like this:
RESULTS TABLE

ID	SKU_new	"extended match" on SKU_old	drawing_ref
1	ABC123	ABC123	54-LP1682
2	ABC123	ABC123	HMPP-14
3	ABC123	ABC123	9999424
4	BC12	ABC123	54-LP1682
5	BC12	ABC123	HMPP-14
6	BC12	ABC123	9999424
7	ABC12	ABC123	54-LP1682
8	ABC12	ABC123	HMPP-14
9	ABC12	ABC123	HMPP-14

Huge apologies if I'm mangling some of the terminology...

(By the way, this is all to help in the tidying up of old data. Hopefully going forward we shouldn't be getting into these tangles!)

Hope to hear from somebody soon

And stay safe out there!
Best
Ellison

 

[cheekybuddha]

Hi Ellison,

You should be able to do something like:

SELECT
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
FROM [NEW TABLE] n
INNER JOIN [OLD TABLE] o
        ON n.SKU_new LIKE '*' & o.SKU_old & '*'
ORDER BY
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
;

hth,

d

 

[theDBguy]

Hi Ellison. That should be possible using a Non-Equi Join. For example:

SELECT SKU_new, SKU_old, drawing_ref
FROM OLD_Table
INNER JOIN New_Table
ON OLD_Table.SKU_old Like "*" & NEW_Table.SKU_new & "*"

Hope that helps...

Edit: Oops, too slow...

 

[theDBguy]

Hi Ellison,

You should be able to do something like:

SELECT
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
FROM [NEW TABLE] n
INNER JOIN [OLD TABLE] o
        ON n.SKU_new LIKE '*' & o.SKU_old & '*'
ORDER BY
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
;

hth,

d

Hi David. I think one of us is backwards.

 

[plog]

I would Instr(https://www.techonthenet.com/access/functions/string/instr.php) to see if SKU_new is in SKU_Old:

SELECT NewTable.SKU_new, OldTable.*
FROM NewTable, OldTable
WHERE (((InStr([SKU_Old],[SKU_new]))>0));

The problem is, since you have no definitive matching fields between your two tables this could take a while to run since it has to compare every SKU_new to every SKU_old.

 

[cheekybuddha]

Oops! I got the join the wrong way round

try:

SELECT
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
FROM [NEW TABLE] n
INNER JOIN [OLD TABLE] o
        ON o.SKU_old LIKE '*' & n.SKU_new & '*'
ORDER BY
  n.SKU_new,
  o.SKU_old,
  o.drawing_ref
;

 

[cheekybuddha]

@DBG,

I just realised before I saw your prompt! Thanks for the heads-up!

d

 

[theDBguy]

@DBG,

I just realised before I saw your prompt! Thanks for the heads-up!

d

I got your back!

 

[cheekybuddha]

I got your back!

You always do, and it's always appreciated!

 

[Hanna]

Hi gents, so appreciated!

I seem to be able to get theDBguy's one running great (& it is probably my own fault that I can't get the other one giving the right looking results- sorrry!)

Mind if I ask a question about the SQL query from the DBguy?

ermmmmm

can I tweak anything in the code so that it will make the "Non-equi join"(hope I spelt that right!) into 3 separate types of join?

i.e. one query for "contains"
a different for "ends with"
and a 3rd one for "begins with"

All help hugely appreciated gents!!!!

Best
Ellison

 

[theDBguy]

Hi gents, so appreciated!

I seem to be able to get theDBguy's one running great (& it is probably my own fault that I can't get the other one giving the right looking results- sorrry!)

Mind if I ask a question about the SQL query from the DBguy?

ermmmmm

can I tweak anything in the code so that it will make the "Non-equi join"(hope I spelt that right!) into 3 separate types of join?

i.e. one query for "contains"
a different for "ends with"
and a 3rd one for "begins with"

All help hugely appreciated gents!!!!

Best
Ellison

Hi Ellison. Glad to hear you got it to work. The one you have does the "contains" query, doesn't it? To do "ends with," you'll have to change the join to use Like "*" & SKU_new. And to do "begins with," you'll have to change it to use Like SKU_new & "*". Hope that helps...

 

[Hanna]

Brilliant, I will have a play around - huge thanks Mr DB guy!

And to you too Mr cheekybuddha .... the revised one works a charm

Wow, this access stuff is amazing! I'm like a kid in a sweet shop haha

 

[cheekybuddha]

DBguy will have a quarter of lemon sherbets, plog will have a some cola cubes and I'll have a quarter of bullseyes, but you can keep the rhubarb and custards!!

Glad you got it working! Don't rot your teeth too quickly!

d

 

[Hanna]

....... although I would gladly send them over, I seem to have scoffed the lot. DRAT!!!
PS Bullseye, old school
PPS did *anybody* like a rhubard and custard?!!!!