Creating Function to re-arrange field

[anzacks]

Anyone has tried this?

I have a table like below:

I want to create a query to rearrange the field like below:

I managed to do that using below function. "ChooseLike" is 1 for No1, 2 for No2 and so on.
...................................................................................................

Option Compare Database
Option Explicit
Public Sel1, Sel2, Sel3, Sel4, Sel5, Sel6, Para1, Para2, Para3, Para4, Para5, Para6 As String
Public ChooseLike As Variant


Function SelectFrom(ChooseLike, Sel1, Sel2, Sel3, Sel4, Sel5, Sel6, Para1, Para2, Para3, Para4, Para5, Para6) As String

If Sel1 = ChooseLike Then
SelectFrom = Para1
ElseIf Sel2 = ChooseLike Then
SelectFrom = Para2
ElseIf Sel3 = ChooseLike Then
SelectFrom = Para3
ElseIf Sel4 = ChooseLike Then
SelectFrom = Para4
ElseIf Sel5 = ChooseLike Then
SelectFrom = Para5
ElseIf Sel6 = ChooseLike Then
SelectFrom = Para6

End If

End Function

..................................................................................................

Does Ms Access has similar function like this?

 

[gemma-the-husky]

i doubt it - the trouble is your data appears to be not normalised, so the only way to get what you want is by using vba - a case statement might be easier to read, but it does the same thing

 

[anzacks]

using vba

i doubt it - the trouble is your data appears to be not normalised, so the only way to get what you want is by using vba - a case statement might be easier to read, but it does the same thing

Hi, thanks for your reply. I do agree with you. That was the first function I created. Still learning. Any suggestion on vba?

 

[Rabbie]

You really need to normalise your data. If you don't you will just make things more difficult for yourself as you progress. With properly normalised data you should be able to create queries with the data in the format you want without too much difficulty.