join tables on fields containing similar text

[gdesreu]

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!

 

[Joe4]

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]

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]

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]

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:

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]

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:

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]

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!