Reverse look-up in SQL 1

[cloverdog]

Hello I wonder if anyone could help with a sort of reverse lookup query where I am starting with a complete string and need to find a row containing a sub string of it in another table.

The first table contains 4 fields from a monthly bank statement (tblBankStatement):
Date, Transaction Type, Amount, Details

The second is a table of customers (tblCustomers) containing
CustID, FirstName, Surname, PartialBankDetails

I wish to write an SQL statement that will return the CustID of all Customers whose details appear on a bank statement.

The problem is details for some customers vary between statements. So for instance June statement for Anderson may show as “ANDERSON PE&JD” but the July statement might show as just “ANDERSON PE” whilst August could be “ANDERSON PE AND JD. So I store the partial details that I know will not change in tblCustomers (eg. PartialBankDetails contains “ANDERSON PE”).

The only way I can think of to do this would be to run a query from VB in a loop and pass in the abbreviated BankDetails for each individual customer in tblCustomer so that when it got to Anderson it would search on ANDERSON * or use InStr() function but I would much rather do this with SQL without VB if possible.

Many thanks in anticipation

 

[Remou]

Something like:

SELECT tblCustomers.ID, tblCustomers.PartialBankDetails, tblBankStatement.Details
FROM tblBankStatement, tblCustomers
WHERE tblBankStatement.Details Like "*" & [tblCustomers].[PartialBankDetails] & "*"

 

[cloverdog]

Thanks for the speedy reply it really helped. I just needed to add a check for "NOT vbnullstring" in the where statement as below (so it didn't return every BankRecord for every single customer giving a record count of statement records * Customers in customer table):

WHERE (Not (tblCustomers.PartialBankDetails)="vbnullstring") AND ((tblBankStatement.Details) Like "*" & [tblCustomers].[PartialBankDetails] & "*");

This is looking good but testing highlights another problem where 2 or more customers on the same statement share the same PartialBankDetails such as THOR and THORNE LJ.


ID PartialBankDetails Details
120 THOR THOR
120 THOR THORNE LJ
123 THORNE LJ THORNE LJ
80 FREEMAN B FREEMAN B JFD A
22 MACKSON PJ MACKSON PJ
100 BIRTON AE BIRTON AE

It would be nice to return the best (exact / longest?) match where >1 client record is returned for tblBankStatement.Details.

Once again many thanks.

 

[Remou]

It may help to add a space to both the details and partial details, though I have not checked carefully:

SELECT tblCustomers.ID, tblCustomers.PartialBankDetails, [Details] & " " AS WithSp
FROM tblBankStatement, tblCustomers
WHERE [Details] & " " Like "*" & [tblCustomers].[PartialBankDetails] & " *"

 

[cloverdog]

Thank's for your help with this Remou adding the space works a treat.