Update query with IIF statement (1 Viewer)

[PMENDOZA]

I have an update query that needs to update data in JAN, FEB, MAR...fields in table A, based on the DATE field in linked table B.
I have the update set to:
JANBUD FEBBUD
IIF(Month([DATE_Field]=1,[BUDGET_AMOUNT],0) IIF(Month([DATE_Field]=2,[BUDGET_AMOUNT],0)
The problem I'm having is that it's only updating the last record, month 12, and nothing before that.
I have records with month numbers 1-12
I have attached the snapshot of the query.
Any help is much appreciated.

 

[theDBguy]

Hi. Try converting your UPDATE query into a SELECT query to see the records you will be updating. If it doesn’t look right, try adjusting your query to get the correct records.

 

[Ranman256]

I would make 12 queries. So they don't interfere w each other.

 

[PMENDOZA]

Thanks. I was trying to avoid the 12 query route, which I know works.
When I run the select query , all the data for the records to be updated is there, but it's only updating the last one.
Thanks.:banghead:

 

[isladogs]

It's not possible to see the whole query so we can't tell why it fails.
However, your Top Account table isn't normalised.
You should not have 12 fields named JANBUD, FEBBUD etc.
Instead have two fields MonthBud and MonthNo or better still YearMonth e.g 1901
This should simplify your work significantly.

 

[PMENDOZA]

Thanks. Will try this.