Update query with IIF statement (1 Viewer)

PMENDOZA · Jan 18, 2019

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 · Jan 18, 2019

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 · Jan 18, 2019

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

PMENDOZA · Jan 18, 2019

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 · Jan 18, 2019

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 · Jan 18, 2019

Thanks. Will try this.

Update query with IIF statement (1 Viewer)

PMENDOZA

New member

Attachments

theDBguy

I’m here to help

Ranman256

Well-known member

PMENDOZA

New member

isladogs

MVP / VIP

PMENDOZA

New member

Similar threads

Users who are viewing this thread