use criteria from a table to update a category in another table.

[Amps]

I have a table that has banking information in it (downloaded from the internet). I have a category field (lookup field) that I have to update manually so, for example, every time the electric bill is paid I have to click it and change the category to "electricity". I want to set up a table with phrases for access to search for and a category to change to. For example if the banking table has "VIS ELECTRICITY 20812/773474868" and my search criteria table says anything with the word "electricity" should have category of "electricity", then I want Access to update the banking table based on that. There will be several items in the search criteria table so Access will have to read through all of them to find the correct one. Is this possible?

I'm not a beginner in Access but I'm not an expert either. When I need to do accomplish something I usually search all over the internet until I find an easy explanation to copy but this time I couldn't find anything exactly like what I want and decided to post.

 

[Mihail]

Welcome to the forum !

Seems to be possible.
At the first view the DLookup function is the ideal candidate for this task.
What about to quickly design a DB (2003 version), fill in some data and upload it ?

 

[Amps]

Thanks for your help Mihail. Here is an example database.

 

[Amps]

In the example Access would use information from tblCriteria to update the category in tblBankingInfo.

 

[Mihail]

Run the query.

Take a look here
Recordsets for Beginners - Access wiki - Access Help and How-to - Microsoft Office by UtterAccess.com

Are you able to continue ?

The logic should be:
Open a recordset based on the query.
From each record extract the transactionID (first field in the query) and the CategoryID (the last field in the query)
Use this in an Update query.

If you can't implement this logic, inform me.

Good luck !

 

[Amps]

Thank you Mihail that is amazing!!! I was convinced that it couldn't be done.
I should be able to do the update query myself.

Thanks again.