Updating Table using Code

[BRANDON99]

Hi

I would like to analyse my spending from the last few years and have downloaded the statements. I have added an extra field, Category which I want to update with code. There is a field, Detail of Operation that identifies each type of transaction giving detail of payment type, card, cheque, transaction date and benefactor. So if this field contains Tesco, I want to update Category field to Food, if the field contains EDF, update to Energy, etc. I know an update query could be used for each but is there a way to code this as I am sure it would be better

Thanks for your help

 

[genomon]

I would create another table with all the acronyms and their meanings, maye an ItemName and Meaning fields. Then you can UPDATE yourtable AS a INNER JOIN newtable AS b ON a.DetailOFOperation = b.ItemName SET a.category = b.meaning
Not tested, just off the cuff...

An làmb a bheir, ‘s i a gheibh.

 

[BRANDON99]

Hi

I only want to match part of the DetailOfOperation field, eg Carte Tesco Gaillac 30/08/2010, the ItemName would be Tesco, Meaning field - Food. Any DetailOfOperation field containing Tesco would cause Category field update to Food

 

[MajP]

You can use like to match fields by part of a field
Something like

SELECT
tblData.Field1,
tblCategories.Cat AS Category
FROM
tblCategories,
tblData
WHERE
tblData.Field1 Like "*" & [tblCategories].[containsText] & "*"

 

[BRANDON99]

Thanks MajP

Can you please explain what else I would need to add to then update the Category field