Update Query, Multiple fields, different criteria

[rogress]

Is there a way to create an update query that updates multiple fields, each with different criteria. Something like:

Update table1
set field1 = "blue"
where table2.code = 5
set field2 = "red"
where table2.code = 3
etc...

I know the above isn't working code (I left out the join, etc), but hopefully it shows what I'm trying to do.

For now, I've created multiple update queries, and put them all in a macro, but you have to respond to each one. I would like to have one consolidated update query.

 

[vbaInet]

How many criteria do you have?

 

[rogress]

Probably 12 or more. 12 fields, each with their own criteria.

 

[vbaInet]

I see you are new here so welcome to the forum!

Is the UPDATE statement working with just one criteria? I mean, do you know how to write a simple UPDATE statement?

 

[rogress]

Thanks for the welcome. Yes, I currently run each of the update queries individually (macro runs one after the other).
Here are two different update queries that I would like to combine, if it is possible:

#1:
UPDATE [ERP Master] INNER JOIN Update_Table ON ([ERP Master].NWA = Update_Table.NWA) AND ([ERP Master].PERNR = Update_Table.PERNR) SET [ERP Master].[1120 1126 Approved] = [Update_Table].[SumOfHours]
WHERE (((Update_Table.Week)="1120-1126"));
#2:

UPDATE [ERP Master] INNER JOIN Update_Table ON ([ERP Master].NWA = Update_Table.NWA) AND ([ERP Master].PERNR = Update_Table.PERNR) SET [ERP Master].[1127 1203 Approved] = [Update_Table].[SumOfHours]
WHERE (((Update_Table.Week)="1127-1203"));

The more I think about it, the more I think this can't be done with one query, I'm updating multiple fields, each with their own criteria. I tried "faking" it in Designer View (see attached) by specifying the unique criteria under each field that I was updating, but of course it change the criteria into a combined where clause.

I'm essentially trying to automate these weekly updates so that we don't have to respond to the multiple update queries which are run individually (maybe I should go into Access options and turn off the update query prompts, if thats possible - didn't think of that until now.....)

 

[vbaInet]

Alright, that's fine. I just wanted to make sure you knew what you were doing.

1. Issue different criteria in the WHERE part of the statement using OR or you can use IN(), e.g.

WHERE Update_Table.Week IN ("1120-1126", "1127-1203")

2. For the SET part you will use the Switch() function:

Switch([FieldName] = 5, "blue", [FieldName] = 3, "Red")

But I fear that your data is not normalized.

 

[rogress]

Thanks, I'll try that and let you know how it works....I do think the data is normalized.

...Roger

 

[vbaInet]

Well Roger, field names like [1127 1203 Approved] indicate that you have many more fields in this format. What you're doing should actually be done using a junction table.