join tables on fields containing similar text

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
306
Is there a way to match similar fields in an access query?
For example, I have a location code with coordinates on one table and on the other table I have sample IDs that contains the location code plus other information. I need to match the sample IDs to the location code in order to populate the best match for coordinate. Is this possible?
Thanks!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
The way that I like to do it is to create a query based on the table that has the sample IDs that contain the location, plus the other information, and create a calculated field that pulls off just the location code.

Then, join this query with your other table in a new query based on this field.

By the way, what you are describing is really not a very good database model. If you have any control over it, I would recommend changing the design so there is a field that the tables can be joined on without having to jump through these hoops.
 

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
306
The way that I like to do it is to create a query based on the table that has the sample IDs that contain the location, plus the other information, and create a calculated field that pulls off just the location code.

Then, join this query with your other table in a new query based on this field.

By the way, what you are describing is really not a very good database model. If you have any control over it, I would recommend changing the design so there is a field that the tables can be joined on without having to jump through these hoops.
Thanks, Ill give it a try. No I don't have any control over it, if I did this problem wouldn't exist. Unfortunately my task is to take data from other companies and merge it. Im thinking to also add another column and tag this with huge letters that says "DRAFT!"
 

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
306
The way that I like to do it is to create a query based on the table that has the sample IDs that contain the location, plus the other information, and create a calculated field that pulls off just the location code.

Then, join this query with your other table in a new query based on this field.

By the way, what you are describing is really not a very good database model. If you have any control over it, I would recommend changing the design so there is a field that the tables can be joined on without having to jump through these hoops.
Also, forgot to mention, I cant extract out the location code because I cant be sure If they are location codes or not so I was hoping to match the known location codes from the other table to the text in these fields. I may pull it out and make a lookup table in excel but even then I think the logic is backwards.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

There is another way that MIGHT work. You can add the two tables to a query, but have no join between them, but then use a WHERE clause to only return records where the SampleID field starts with the Location field value.

That code might look something like this:
Code:
SELECT Table1.Location, Table2.SampleID
FROM Table1, Table2
WHERE Table2.SampleID Like Table1.Location & "*";

Where you might run into issues is if you have location codes that are similar, i.e. one is contained in another location code (such as "abc" and "ab", as and SampleID that starts with "ab" will match up to BOTH of those locations).
 

gdesreu

Active Member
Joined
Jul 30, 2012
Messages
306
There is another way that MIGHT work. You can add the two tables to a query, but have no join between them, but then use a WHERE clause to only return records where the SampleID field starts with the Location field value.

That code might look something like this:
Code:
SELECT Table1.Location, Table2.SampleID
FROM Table1, Table2
WHERE Table2.SampleID Like Table1.Location & "*";

Where you might run into issues is if you have location codes that are similar, i.e. one is contained in another location code (such as "abc" and "ab", as and SampleID that starts with "ab" will match up to BOTH of those locations).
This is exactly what I did. I was just coming back to post a possible workaround for reference. As you stated it is flawed but Im hoping the location codes are unique enough where it wont be an issue, however, murphys law dictates this will make my life miserable tomorrow. Why people assemble their data this way I'll never know, but I see it more often than not. I guess I shouldn't complain its keeping me employed. :) thanks again for the help!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,916
Office Version
  1. 365
Platform
  1. Windows
Yes, I feel your pain. It is very frustrating to have to work with a poorly designed database (or data) that you have no control over. It happens much more than I would like it to!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,441
Members
414,065
Latest member
kamlkham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top