User Select Field

[gator98bcn53]

I have a selection query combining a few different queries. What I
need to do is write a query that takes this information and returns two
collumns(fields) from this query that a user selects to create the
results for a report. Here is what I am trying to do.

Data query
Seq # Item Field 1 Field 2 Field 3 Field 4 Field 5

Result Query
This is what I want

Seq # Item Field1 Choose(field 2,3,4,or5) Choose(field 2,3,4,or 5)

 

[John Spencer (MVP)]

Your query would look something like statement below.

SELECT [Seq#], Item,
Choose(Forms!SomeFormName!FirstControl,Field1,Field2,Field3,Field4,Field5) as FirstChoice,
Choose(Forms!SomeFormName!SecondControl,Field1,Field2,Field3,Field4,Field5) as SecondChoice
FROM TheSourceQuery

You would need an open form with two controls on it that will return a numeric
value between 1 and 5. That will allow the choose function to select one of the
five fields to return for each column.

 

[Trey]

I tried this and it would prompt me for every field, why? I am new to
writing in sql can you explain the logic briefly?

 

[John Spencer (MVP)]

Did you replace the generic Field1, Field2, etc with the actual names of the fields?

The logic is that the Choose function says to take the value represented by the
number. That is if the value of the first item is 2, then take the 2nd choice
in the list. You should be able to look up the choose function in the VBA help.

 

[John Spencer (MVP)]

Forgot to add that you may need to fully qualify the field names.

SELECT [TheSourceQuery].[Seq#], [TheSourceQuery].[Item],

Choose(Forms!SomeFormName!FirstControl,[TheSourceQuery].[Field1],[TheSourceQuery].[Field2],...)
as FirstChoice,
...
FROM TheSourceQuery