Combining multiple update queries

[smalljaws]

Hello all, I am new to Access with very basic understanding of the program and databases in general. I need to change the values in a field (Trans) based on date (LocDate). There are roughly 20 values with thousands of records that need updating using different dates for each. I can do this individually with the query builder, but it seems there should be a way to combine these queries. Below are three of the individual queries, how would I go about combining these into one routine? Thanks.

UPDATE LocData SET LocData.Trans = 2.5
WHERE (((LocData.Trans)=2) AND ((LocData.LocDate)>#3/19/2014#));



UPDATE LocData SET LocData.Trans = 3.5
WHERE (((LocData.Trans)=3) AND ((LocData.LocDate)>#4/1/2014#));

UPDATE LocData SET LocData.Trans = 4.5
WHERE (((LocData.Trans)=4) AND ((LocData.LocDate)>#4/8/2014#));

[June7]

Does this UPDATE have to be done regularly or is this a one-time effort? 20 values so I guess that means 20 different criteria? That's too much for an IIf expression. Could possibly write a function that could be called by a single UPDATE action.

UPDATE LocData SET Trans = GetData([Trans], [LocDate]);

Now build the GetData function. Use Select Case or If Then Else structure.

[smalljaws]

Does this UPDATE have to be done regularly or is this a one-time effort? 20 values so I guess that means 20 different criteria? That's too much for an IIf expression. Could possibly write a function that could be called by a single UPDATE action.

UPDATE LocData SET Trans = GetData([Trans], [LocDate]);

Now build the GetData function. Use Select Case or If Then Else structure.

Yes, 20 different criteria. It will need to be run three or four times this year. I don't know SQL so I'm not sure if what you are suggesting would require more effort than just building and executing the 20 individual updates a few times this year? I was planning to do just that, but thought I would check to see if there was an easy way to combine the queries. Thanks for the response.

[June7]

A VBA procedure is the only option I can see. The custom function I describe is one possibility.

The complication I see is that the function will have to be edited for each run because the date criteria will change. Or is the date criteria really important? It actually looks like you just want to add 0.5 to each Trans value.

[smalljaws]

Unfortunately, the date is very important. The trans values only after a specific date.