Find records in one table that doesn't exist in another (fuzzy match) (1 Viewer)

[Sun_Force]

This is an extension to this question.
Here I have the same problem with a slightly different situation.

The following is two tables in a database

tblTemp
TempPK AutoNumber
PsNo String

tblOrders
OrderPK AutoNumber
PsNo String


Both tables have several more fields, but the only common field between them is PsNo. No relationship between them.

What @Minty and @isladogs suggested in previous thread, searches for an exact match between PsNo in two tables.
Here I'm looking for a way to do the same thing but with a fuzzy match.
I need to check for only 9 digits of PsNo in both tables (from left) and not the whole string..
Let me give you an example:

tblTemp

TempPK	PsNo
1	245389447-00
2	245717248-00
3	245730245-00

tblOrders

OrdersPK	PsNo
1	245389447-00*
2	245389447-00A
3	245730245-00
4	245730245-01
5	245730245-00B

The query must return only the second record of tblTemp, because left 9 digit of 245717248-00 doesn't exits in tblOrders.
But left 9 digit of first record has 2 occurrence and the third record has 3 occurrence in tblOrders.

I know this situation is very odd, but any kind of help is appreciated.

 

[theDBguy]

Well, one way to do it is to create two separate queries based on each table where you can use the Left() function to add a calculated column showing the first 9 digits. You can then use the same technique you learned earlier; but instead of comparing two tables, you will be comparing two queries.

 

[Sun_Force]

Well, one way to do it is to create two separate queries based on each table where you can use the Left() function to add a calculated column showing the first 9 digits. You can then use the same technique you learned earlier; but instead of comparing two tables, you will be comparing two queries.

thanks for replying.
I already had done that before posting. But the result never shows up. I waited for more than 15 minutes but seems that left function takes a long time to work on 138847 records. I had to give up and kill Access process from the task manager.

I

 

[theDBguy]

thanks for replying.
I already had done that before posting. But the result never shows up. I waited for more than 15 minutes but seems that left function takes a long time to work on 138847 records. I had to give up and kill Access process from the task manager.


View attachment 90035 I

Hi. I am using my phone, so please pardon any typos but try:

SELECT TempPK
FROM qryTemp
INNER JOIN qryOrders
ON qryTemp.PS=qryOrders.PS
WHERE qryOrders.PS Is Null

(untested)
Sent from phone...

 

[arnelgp]

SELECT tblTemp.TempPK, tblTemp.PsNo
FROM tblTemp LEFT JOIN tblOrders ON tblTemp.PsNo = Left$(tblOrders.PsNo, Len(tblTemp.PsNO))
WHERE tblOrders.PsNo Is Null


*Note:
you must paste in SQL view. it will complain in Design view.

Edit:
you have somewhat same requirement here:
Solved - How to trim postcode.. | Access World Forums (access-programmers.co.uk)

 

[theDBguy]

SELECT tblTemp.TempPK, tblTemp.PsNo
FROM tblTemp LEFT JOIN tblOrders ON tblTemp.PsNo = Left$(tblOrders.PsNo, Len(tblTemp.PsNO))
WHERE tblOrders.PsNo Is Null


*Note:
you must paste in SQL view. it will complain in Design view.

Edit:
you have somewhat same requirement here:
Solved - How to trim postcode.. | Access World Forums (access-programmers.co.uk)

Oops! Thanks for the assist @arnelgp. I meant to use a LEFT OUTER JOIN. Cheers!

 

[Sun_Force]

*Note:
you must paste in SQL view. it will complain in Design view.

Even in sql view:

I changed it to tblOrders.PsNo="" but now the result is blank. I know I have 10 or 11 unmatched result. And the query fails to show them.
At present I'm doing this process with a function. I hoped a query being faster. But the above query took several minutes to show the result.

I attached a database for playing with.

thanks again for your help.