1

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

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

Expecting to get this table

Table3 (BankReady)

ACCOUNTNR DESCRIPTION REFERNCE 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 Purshase 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
Andre
  • 26,751
  • 7
  • 36
  • 80
gunther herremans
  • 11
  • 2

1 Answers1

1

I think you need to do this with separate UDPATE statements, one for each row in the Codes table.

First insert all TmpBankImport records into BankReady, with NULL for TOCHECK.

Then do a recordset loop over Codes, and build each SQL in VBA like this:

strSearch = " '*" & RS!SEARCHFOR & "*'"

S = "UPDATE BankReady " & vbCrLf & _
    "SET CODE = '" & RS!CODE & "', TOCHECK = '" &  RS!TOCHECK & "'" & vbCrLf & _
    "WHERE TOCHECK IS NULL AND " & vbCrLf & _       ' make sure to only process new records
    " (    ACCOUNTNR   LIKE " & strSearch & vbCrLf & _
    "   OR DESCRIPTION LIKE " & strSearch & vbCrLf & _
    "   OR REFERNCE    LIKE " & strSearch & ")" 

Debug.Print S    ' see https://stackoverflow.com/a/1099570/3820271
DB.Execute S

And finally process all rows that didn't find any match:

UPDATE BankReady SET TOCHECK = 'Y' WHERE TOCHECK IS NULL

If anything in the Codes table may ever include an ', use Gustav's CSql() function instead of concatenating the apostrophes.

If you have multiple search expressions per Code, add them as separate rows in the Codes table. Then you need to process them from specific to generic.
E.g. first REF:8888123, then REF:888

You can do this by adding a Sorting column that you fill manually.
Or if all variations are like the example, you can load the Codes recordset loop with this sort order:

ORDER BY Code, Len(SEARCHFOR) DESC

(First ordering by code makes debugging easier.)

Andre
  • 26,751
  • 7
  • 36
  • 80
  • Thank you for the Answer that helped a lot. How to do this with a SEARCHFOR1 and SEARCHFOR2 field so that if SEARCHFOR2 has also a value then both values should be found to give it a code ? This to rule out if i have a 'REF:888' SEARCHFOR value and there would be a REF:888 and a REF:8888123 record it would find both. But with a second SEARCHFOR value i could filter it better. But the SEARCHFOR2 is not always given, , it can be empty (null). Thanks for your time. – gunther herremans Jun 11 '23 at 01:25
  • That would be bad design. 1..n data points should always be rows, not columns. See edited answer. @guntherherremans – Andre Jun 11 '23 at 20:25