How to search for all values in one table in multiple fields in another table and update or create a new table in ms access?

HighLive · Jun 9, 2023

I have a problem and can't find a solution or don't know how to describe it. I'm trying to create an ms access database for our accounting. I import a bank transfers summary from our bank as a csv file and import it temporary to Table2 (TmpBankImport). Now I would like to automate and add a code to the recurring transactions. I want to do this by creating a table Table1 (Codes) where i put in a unique string value that is a part of the value from different fields of the Table2 (TmpBankImport) AKA ACCOUNTNR, DESCRIPTION, REFERENCE . So as a result I want to create a table like Table3 (BankReady) where the CODE and TOCHECK is set . How can I search for all the keywords that are in table1 in all the Table2 fields and create Table3 in ms access 2016. Could you guys help me on the way ?

Thanks for the help.

here an example of the database

EDIT: The example was just an example because my real database was to complex to write it down. I put the real database in attachment. Please take a look at that one.

Tabel1 (Codes)

SEARCHFOR	CODE	TOCHECK
ALDI	ALD	N
PAYPAL	PAP	Y
LIDL	LDL	N
REF:888	DDL	N
BE88.8888.888	SSD	Y
0123456	BBL	N

Table2 (TmpBankImport)

ACCOUNTNR	DESCRIPTION	REFERENCE
BE22.2222.222	Purchase ALDI	REF: 111111111
BE33.3333.333	Purchase BRICO	PURCHASE 0123456
BE44.5555.777	PayPal (Europe)	S. C.MED: 999999
BE11.1234.123	SECURITY SERV.	SERVICE NR:23232323
BE22.2222.222	Purchase ALDI	REF: 22222222
BE33.4444.555	Purchase LIDL	SOME REFERENCE Nr
BE77.7777.777	GOOGLE INC	Google App REF:888
BE88.8888.888	PC ONLINE SHOP	Reference ID:blabla

This is the table that i want to achieve

Table3 (BankReady)

ACCOUNTNR	DESCRIPTION	REFERENCE	CODE	TOCHECK
BE22.2222.222	Purchase ALDI	REF: 111111111	ALD	N
BE33.3333.333	Purchase BRICO	PURCHASE 0123456	BBL	N
BE44.5555.777	PayPal (Europe)	S. C.MED: 999999	PAP	Y
BE11.1234.123	SECURITY SERV.	SERVICE NR:23232323		Y
BE22.2222.222	Purchase ALDI	REF: 22222222	ALD	N
BE33.4444.555	Purchase LIDL	SOME REFERENCE Nr	LDL	N
BE77.7777.777	GOOGLE INC	Google App REF:888	DDL	N
BE88.8888.888	PC ONLINE SHOP	Reference ID:blabla	SSD	Y

June7 · Jun 9, 2023

Should the CODE "PAP" in CODES table by "PAY"? You show "PAY" in desired output.

Fairly simple to return records that have a match but trick is to also include AccountNr records that don't have match in SearchFor, as is the case with BE11.1234.123. Consider:

SELECT AccountNr, [Description], Reference, Code, ToCheck
FROM TmpBankImport, Codes
WHERE AccountNr=SearchFor OR InStr(Description, SearchFor)>0 OR InStr(Reference, SearchFor)>0
UNION SELECT AccountNr, [Description], Reference, Null, True FROM TmpBankImport WHERE NOT AccountNr IN (SELECT AccountNr FROM TmpBankImport, Codes
WHERE AccountNr=SearchFor OR InStr(Description, SearchFor)>0 OR InStr(Reference, SearchFor)>0);

Beware, this involves a Cartesian product query which can cause slow performance with large dataset.
Alternative approach might require VBA.

The_Doc_Man · Jun 9, 2023

You have what appears to be a normalization problem. Your table 2 APPEARS to be a complete subset of table 3, i.e. there is no field in table 2 that ISN'T in table 3. Among other things, the question would come up regarding discussions of which table was definitive and which one was derivative. You would begin double-entry bookkeeping very quickly if you leave it that way. IF it happens that your descriptions of table 2 and table 3 are incomplete and they DO have different fields, then just remember we need complete info to give best advice.

The normal way to do something like this might be to just create table 2 but with extra fields (as shown in table 3) that aren't initially filled in, then you run your searches to do the updates and trigger the fill-in later.

You could do your search using a WHERE clause that includes tests of more than one column,

Code:

... WHERE "*" & search-item & "*" LIKE first-field-name OR "*" & search-item & "*" LIKE second-field-name ...

It might be possible to construct a couple of queries to return an ACCOUNTNR based on partial matches and then just do the update via a JOIN as a layered query. I don't think you can do this in a single step because Access SQL limits what you can do in JOIN ... ON clauses, whereas other vendors would allow an expression to power the ON clause.

theDBguy · Jun 9, 2023

Hi. Welcome to AWF!

HighLive · Jun 10, 2023

June7 said:
Should the CODE "PAP" in CODES table by "PAY"? You show "PAY" in desired output.

Fairly simple to return records that have a match but trick is to also include AccountNr records that don't have match in SearchFor, as is the case with BE11.1234.123. Consider:

SELECT AccountNr, [Description], Reference, Code, ToCheck
FROM TmpBankImport, Codes
WHERE AccountNr=SearchFor OR InStr(Description, SearchFor)>0 OR InStr(Reference, SearchFor)>0
UNION SELECT AccountNr, [Description], Reference, Null, True FROM TmpBankImport WHERE NOT AccountNr IN (SELECT AccountNr FROM TmpBankImport, Codes
WHERE AccountNr=SearchFor OR InStr(Description, SearchFor)>0 OR InStr(Reference, SearchFor)>0);

Beware, this involves a Cartesian product query which can cause slow performance with large dataset.
Alternative approach might require VBA.

Indeed, PAY should be PAP, I edited and removed the typo. I will give this a try and come back with the results. The idea is to perform the query after the import in the temporary table before it goes to the production table. That way it it doesn't matter that it is a little slow at that moment and will still be faster than checking every transaction manually. The Query will not be executed to the whole database. But if anybody has a VBA solution then this would be welcome also. Thank you for your time.

CJ_London · Jun 10, 2023

your example data is showing clearly different 'search for' values. But if for example you had search for values of REF:888 and REF:8889 then REF:888 would be duplicated as it appears in both if the import has a REF:8889 transaction.

If this is possible now or in the future, you need an additional query to check that each search for value is truly unique and not repeated in other search for values.

query is simple enough to identify if this is an issue

Code:

SELECT *
FROM Codes A, Codes B
WHERE A.searchfor like "*" & B.searchfor & "*" AND A.searchfor<>B.searchfor

If you have these duplicates you need to find a workaround. One of my clients is a charity who receive many DD donations each month. Their problem is they have a donor A Bennett and another LA Bennett. Each donor is donating to a different fund within the charity, so as well as the need to avoid duplicates, the relevant fund needs to be identified (similar to identifying your code value). No problem for LA Bennett, but it is for A Bennett.

The solution is to identify another key element of the transaction from the import. Might be the day of the month the DD is set up for, might be some other unique value in the transaction such as the value or other reference, or perhaps where the name appears in the transaction (e.g. at the beginning or end, always X number of chars from the beginning etc). In your case maybe which column the value is found. In my clients case this works 95% of the time, the remaining 5% are not 'posted' but flagged as requiring manual intervention

HighLive · Jun 10, 2023

The_Doc_Man said:
You have what appears to be a normalization problem. Your table 2 APPEARS to be a complete subset of table 3, i.e. there is no field in table 2 that ISN'T in table 3. Among other things, the question would come up regarding discussions of which table was definitive and which one was derivative. You would begin double-entry bookkeeping very quickly if you leave it that way. IF it happens that your descriptions of table 2 and table 3 are incomplete and they DO have different fields, then just remember we need complete info to give best advice.

The normal way to do something like this might be to just create table 2 but with extra fields (as shown in table 3) that aren't initially filled in, then you run your searches to do the updates and trigger the fill-in later.

You could do your search using a WHERE clause that includes tests of more than one column,

Code:

... WHERE "*" & search-item & "*" LIKE first-field-name OR "*" & search-item & "*" LIKE second-field-name ...

It might be possible to construct a couple of queries to return an ACCOUNTNR based on partial matches and then just do the update via a JOIN as a layered query. I don't think you can do this in a single step because Access SQL limits what you can do in JOIN ... ON clauses, whereas other vendors would allow an expression to power the ON clause.

Both tables are temporary and will be cleaned after it's import to production table. It is a bank transaction summery that i will download weekly, First i have to cleanup the import because the first 10 lines are a bunch of information from the bank. I have attached the ms access database with an snipped example of that cleaned up bank import table (acc_tbl_TempBankImport) and the Code table (acc_tbl_CreditCodes). I have to find a string that is unique out of the fields (Naam tegenpartij bevat) or (Transactie) or (mededelingen) to identify the transaction and put it in the table (acc_tbl_CreditCodes) and give it a code so i can make queries to search on those codes and make year rapports ans so on.

The idea is that after the codes are inserted i can put the ones with code in the final production table and the ones that haven't passed 'the insert code query' to be looked after manually and if these are transactions that will return in later imports, then i have to find a snipped string that i put in the (acc_tbl_CreditCodes) to edentify them in the future. I must say, i'm a ms access beginner but i'm an IT'er with some basic knowledge of programming. And willing to learn. I hope it is a clear explenation because English is not my native language. Thank you for your time.

HighLive · Jun 10, 2023

CJ_London said:
your example data is showing clearly different 'search for' values. But if for example you had search for values of REF:888 and REF:8889 then REF:888 would be duplicated as it appears in both if the import has a REF:8889 transaction.

If this is possible now or in the future, you need an additional query to check that each search for value is truly unique and not repeated in other search for values.

query is simple enough to identify if this is an issue

Code:

SELECT * FROM Codes A, Codes B WHERE A.searchfor like "*" & B.searchfor & "*" AND A.searchfor<>B.searchfor

If you have these duplicates you need to find a workaround. One of my clients is a charity who receive many DD donations each month. Their problem is they have a donor A Bennett and another LA Bennett. Each donor is donating to a different fund within the charity, so as well as the need to avoid duplicates, the relevant fund needs to be identified (similar to identifying your code value). No problem for LA Bennett, but it is for A Bennett.

The solution is to identify another key element of the transaction from the import. Might be the day of the month the DD is set up for, might be some other unique value in the transaction such as the value or other reference, or perhaps where the name appears in the transaction (e.g. at the beginning or end, always X number of chars from the beginning etc). In your case maybe which column the value is found. In my clients case this works 95% of the time, the remaining 5% are not 'posted' but flagged as requiring manual intervention

I know i have to find a way to identify a unique string and maybe with a second identifier field in the code table that will lookup with a AND statement that will be executed if there is a value in the second identifier field. The database that i have uploaded is my personal edited version to learn how to get those to work on my final accounting database . This is also for a charity organization that receives gifts,donations,subsidy,membership fee, etc .. Thank you for your time.

HighLive · Jun 11, 2023

I found a VBA solution for the problem. Please take a look at the msaccess db in attachment of this message.

I created a module ( WalkTruhCodes) and that does the job pretty well for looking after the searchfor1 values and add the code to the table. But now i have the following question. How do i do it when there is also a value in "searchFor2" ?

So now it walks trough the 'acc_tbl_CreditCodes' table to get the SearchFor1 Value and when it finds it he puts the 'code' and the 'ToCheck' fields in the 'acc_tbl_TempBankImport' the 'ToCheck' of that table has to be Null so that after the code runs trough it the leftovers where nothing is found is still null. Right now when you run it the check for both fieldsearch doesn't work. It gives only the BCG code. But i would like to get the BCE code if it finds 'Bancontact' and 'Evelien" in one of those fields. So there must be an AND statement somehow. But How ?

The code that i have now is this:

Sub WalkTruhCodes()

Dim db As Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("acc_tbl_CreditCodes")
Do Until rst.EOF

strSearch1 = " '*" & rst!SEARCHFOR1 & "*'"

S = "UPDATE acc_tbl_TempBankImport " & vbCrLf & _
"SET CODE = '" & rst!CODE & "', TOCHECK = '" & rst!TOCHECK & "'" & vbCrLf & _
"WHERE TOCHECK IS NULL AND " & vbCrLf & _
" ( [Rekening tegenpartij] LIKE " & strSearch1 & vbCrLf & _
" OR [Naam tegenpartij bevat] LIKE " & strSearch1 & vbCrLf & _
" OR [Transactie] LIKE " & strSearch1 & vbCrLf & _
" OR [Mededelingen] LIKE " & strSearch1 & ")"

Debug.Print S
db.Execute S
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Eugene-LS · Jun 11, 2023

HighLive said:
Right now when you run it the check for both fieldsearch doesn't work.

Try:

Code:

Sub WalkTruhCodes()
Dim rst As DAO.Recordset
Dim strQ As String, strSQL As String
Dim strSearch$
    
    strQ = "UPDATE (" & vbCrLf & _
            "   SELECT CODE, TOCHECK, " & vbCrLf & _
            "       ([Rekening tegenpartij]+' ') & ([Naam tegenpartij bevat]+' ') & " & vbCrLf & _
            "       ([Transactie]+' ') & ([Mededelingen]+' ') AS SrFLD " & vbCrLf & _
            "   FROM acc_tbl_TempBankImport " & vbCrLf & _
            "   WHERE (TOCHECK Is Null)" & ") AS SubQuery "

        
    Set rst = CurrentDb.OpenRecordset("acc_tbl_CreditCodes")
    With rst
        Do Until .EOF
            strSearch = "(SrFLD Like '*" & !SEARCHFOR1 & "*')"
            If IsNull(!SEARCHFOR2) = False Then
                 strSearch = "(" & strSearch & " OR (SrFLD Like '*" & !SEARCHFOR2 & "*'))"
            End If

            strSQL = strQ & vbCrLf & _
                "SET CODE = '" & !CODE & "', TOCHECK = '" & !TOCHECK & "'" & vbCrLf & _
                "WHERE " & strSearch

            'Debug.Print strSQL
            CurrentDb.Execute strSQL
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
End Sub

HighLive · Jun 11, 2023

Eugene-LS said:

Try:

Code:

Sub WalkTruhCodes()
Dim rst As DAO.Recordset
Dim strQ As String, strSQL As String
Dim strSearch$

    strQ = "UPDATE (" & vbCrLf & _
            "   SELECT CODE, TOCHECK, " & vbCrLf & _
            "       ([Rekening tegenpartij]+' ') & ([Naam tegenpartij bevat]+' ') & " & vbCrLf & _
            "       ([Transactie]+' ') & ([Mededelingen]+' ') AS SrFLD " & vbCrLf & _
            "   FROM acc_tbl_TempBankImport " & vbCrLf & _
            "   WHERE (TOCHECK Is Null)" & ") AS SubQuery "

    
    Set rst = CurrentDb.OpenRecordset("acc_tbl_CreditCodes")
    With rst
        Do Until .EOF
            strSearch = "(SrFLD Like '*" & !SEARCHFOR1 & "*')"
            If IsNull(!SEARCHFOR2) = False Then
                 strSearch = "(" & strSearch & " OR (SrFLD Like '*" & !SEARCHFOR2 & "*'))"
            End If

            strSQL = strQ & vbCrLf & _
                "SET CODE = '" & !CODE & "', TOCHECK = '" & !TOCHECK & "'" & vbCrLf & _
                "WHERE " & strSearch

            'Debug.Print strSQL
            CurrentDb.Execute strSQL
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
End Sub

Didn't do the trick. I still get only the BCG code if (SearchFor1 = BANCONTACT) and SearchFor2=Gunther)
en no BCE but BCG code instead if (SearchFor1 = BANCONTACT) and SearchFor2=Evelien). Thank you for your answer and time.

EDIT: I found what the reason was.

I changed the OR in AND in your code and that did the trick. So it should be.

If IsNull(!SEARCHFOR2) = False Then
strSearch = "(" & strSearch & " AND (SrFLD Like '*" & !SEARCHFOR2 & "*'))"
End If

Thank you so much.

Eugene-LS · Jun 11, 2023

HighLive said:
I changed the OR in AND in your code and that did the trick. So it should be.

Okay, perhaps I just misunderstood your task.
Good luck!

CJ_London · Jun 12, 2023

Not sure why you need a VBA loop when what you want can be achieved with a query. Based on the example data in your first post, see attached.

June7 · Jun 12, 2023

CJ, why don't you post query statement? Why do we have to download file just to see SQL? You can provide file in addition to text.

CJ_London · Jun 12, 2023

CJ, why don't you post query statement?

sure

Code:

INSERT INTO tblBnkReady ( ACCOUNTNR, DESCRIPTION, REFERENCE, CODE, TOCHECK )
SELECT tblBankImport.ACCOUNTNR, tblBankImport.DESCRIPTION, tblBankImport.REFERENCE, M.CODE, Nz([M].[TOCHECK],"Y") AS chk
FROM (tblBankImport 
    LEFT JOIN (SELECT tblBankImport.ACCOUNTNR, tblBankImport.REFERENCE, tblCodes.CODE, tblCodes.TOCHECK FROM tblBankImport, tblCodes WHERE (((tblBankImport.ACCOUNTNR) Like "*" & [Searchfor] & "*")) OR (((tblBankImport.DESCRIPTION) Like "*" & [Searchfor] & "*")) OR (((tblBankImport.REFERENCE) Like "*" & [Searchfor] & "*")))  AS M 
      ON (tblBankImport.REFERENCE = M.REFERENCE) AND (tblBankImport.ACCOUNTNR = M.ACCOUNTNR)) 
  LEFT JOIN tblBnkReady 
     ON (tblBankImport.REFERENCE = tblBnkReady.REFERENCE) AND (tblBankImport.ACCOUNTNR = tblBnkReady.ACCOUNTNR)
WHERE (((tblBnkReady.ID) Is Null));

How to search for all values in one table in multiple fields in another table and update or create a new table in ms access?

HighLive

New member

Attachments

June7

AWF VIP

The_Doc_Man

Immoderate Moderator

theDBguy

I’m here to help

HighLive

New member

CJ_London

Super Moderator

HighLive

New member

Attachments

HighLive

New member

HighLive

New member

Attachments

Eugene-LS

Registered User.

HighLive

New member

Eugene-LS

Registered User.

CJ_London

Super Moderator

Attachments

June7

AWF VIP

CJ_London

Super Moderator

Users who are viewing this thread