rearrange values using update query

[krisleech]

I am trying to design a update query that will rearrange the values (a range from 1-4) in a field in reverse order.
So 1 become 4, 2 becomes 3, 3 becomes 2, 4 becomes 1.

I thought the way to do this would be to create an update query with the field in 4 times and the criteria for each goes from 1 to 4 and the update goes from 4 to 1. Easy?

But when I run the query I get an error (Duplicate Output Destination 'Field Name') and when I reopen the query Access has changed the criteria in the first instance of the field to "1 And 2 And 3 And 4", and left the other criteria's blank.

Anyone know why this is?

Best wishes, Kris Leech

PS. I know i could create 4 queries to do each update and call them from a macro, but i dont really wanna do that.

 

[Rich]

Maybe too simple, but why not just order Desc?

 

[krisleech]

thanks, but the values in the fields actually need to be changed, it is for statistics calculated later on in another query. To preform the statistics query i need to reverse the values in the field.
Sorting them in descending order would visually change the order but not actually.

Best wishes Kris.

 

[Rich]

Function GetNumb(MyNum As Integer) As Integer

Select Case MyNum
Case 1
GetNumb = 4
Case 2
GetNumb = 3
Case 3
GetNumb = 2
Case 4
GetNumb = 1


End Select
End Function

 

[ColinEssex]

Hi

I would create another field and use update queries then use the new field for your stats.

If you try to update the original field with 4 queries it'll get confused because if you change 1 to 4 with the first query, when it gets to doing the 4 to 1 query it'll change back the ones you did with query 1.

Alternatively you could use nested IIf statements in the stats query which would leave the original figures in the table.

or

Why not put the "real" values in the table in the first place?

Col

 

[krisleech]

Rich - im not good at visual basic so cant use that method

Colin - i have tryed that, but the same orignal problem occurs. I created a new field and then used an update query to put the recoded values into the new field:

Field: oldfield newfield oldfield newfield oldfield

Update to: 4 3 etc.
Criteria: 1 2 3

but i get an error and when i reopent he query it has been changed to:

Field: newfield newfield newfield oldfield

Update to: 1 2 3
Criteria: 1 And 2 And 3

just like my orignal problem

Any surgestions? Kris.

 

[Rich]

There's never a better time to get your feet wet
In this case it's very simple, I assume you want to keep the original values but want to use the other values elsewhere,
simply copy the function as posted into a new Module, compile and save, give the module any name you like, to use the function just add your original table to the query design grid, add
GetNumb([YourField]) the changed values will now be available from that query for use elsewhere, in fact you can add it to any query that your using for statistics

 

[krisleech]

got my feet wet with VB and it works a treat... thanks Rich
Now just gotta descide if to actually change the values in the field or use the function at runtime.


kris.