asked on

Create a query with a partial match join

I would like to create a select query that will show where fields may have a partial match
Ie
Field X on table y has data like 1234/2567
Field a on table b has data like 2567

I know that you can do a link whereby the data is the same in each field on the table, although I dont seem to be able to remove the blanh field and it matches these, have tried putting Is Not Null in the criteria but I still see all the matched blank data

So have 2 issues here
Any suggestions

Gordon

Ryan

I think you mean...

Select *
FROM tblY INNER JOIN tblB ON tblB.a LIKE %tblY.X%

SOLUTION

Rey Obrero (Capricorn1)

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Gordon Hughes

ASKER

Hi

I have used the following SQL statement

SELECT DISTINCTROW [Base Data Chard].Attribute3, [Base Data Chard].[Oracle No], [A DairyCrest Master Unique Data].Noun, [A DairyCrest Master Unique Data].Attribute1T, [A DairyCrest Master Unique Data].Attribute2T, [A DairyCrest Master Unique Data].Attribute3T, [A DairyCrest Master Unique Data].Attribute4T, [A DairyCrest Master Unique Data].Attribute5T, [A DairyCrest Master Unique Data].Attribute6T, [A DairyCrest Master Unique Data].Attribute7T, [A DairyCrest Master Unique Data].Attribute8T, [A DairyCrest Master Unique Data].Attribute9T, *
FROM [Base Data Chard], [A DairyCrest Master Unique Data]
WHERE ((([Base Data Chard].Attribute3) Like "*" & [A DairyCrest Master Unique Data].[Attribute3T] & "*" And ([Base Data Chard].Attribute3) Is Not Null));

but it not working, it gives me thousands of records, I think there is something wrong with the link ti the DairyCrest Master Unique Data table. I think it is giving me the item in the [Base Data Chard].Attribute3 table muliplied by each item in the DairyCrest Master Unique Data query

Gordon

Rey Obrero (Capricorn1)

post a sample db with those tables..

Ryan

I see you're listing out those 9 Fields calls Attribute, then you're selecting all (*). Those added fields (all those specifically called will be duplicated), may be causing your Distinctrow to not filter as much. (I might be wrong as I don't use DISTINCTROW)

I'm not sure if doing the CROSS JOIN with a Where filter works with DistinctRow vs using an INNER JOIN.

Try replacing the end of query (Both the FROM and the WHERE clauses) with:

FROM [Base Data Chard] INNER JOIN [A DairyCrest Master Unique Data] ON
[Base Data Chard].Attribute3 Like "*" & [A DairyCrest Master Unique Data].[Attribute3T] & "*" And ([Base Data Chard].Attribute3 Is Not Null);

Gordon Hughes

ASKER

Hi
I removed the * and replaced the FRM and WHERE parts as per last post
But get JOIN expression not supported

So as capricorn1 says have added a sampple DB with the tables and fields
Sample.zip

Gordon Hughes

ASKER

Guys

Any luck sorting my problem

Gordon

SOLUTION

Ryan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

ASKER CERTIFIED SOLUTION

Gordon Hughes

membership