How to call field dynamically

[mckenneyj]

I have a table with approx 30 fields
10 of those fields are Y/N optional events people can sign up for. Feild Names are OE01 thru OE10
I need to produce a single parameter query that can reference any 1 of those 10 fields to produce a single report.
I'd rather not design 10 querys.
For example today I want a report where OE03 = -1
Maybe tomorrow I want OE07 = -1


Thanks,
John McKenney
Work Hard... Play Harder

 

[mckenneyj]

p.s.
To further clarify
The first parameter prompt when run should be What is the event field you want to use?
The user then inputs OE03
That should tell the query to use the field OE03 and that value = -1 or Yes

Thanks,
John McKenney
Work Hard... Play Harder

 

[dhookom]

You have created a spreadsheet rather than a relational database. Rather than 10 similar fields, I recommend a related table with potentially 10 records per original record. If you can't change your table structure, you can use a union query to normalize your data.

SELECT [FieldA], [FieldB],..., [OE01] as TheValue, "OE01" as TheField
FROM tblNoNameGiven
UNION ALL
SELECT [FieldA], [FieldB],..., [OE02], "OE02"
FROM tblNoNameGiven
UNION ALL
SELECT [FieldA], [FieldB],..., [OE03], "OE03"
FROM tblNoNameGiven
-- etc --
UNION ALL
SELECT [FieldA], [FieldB],..., [OE10], "OE10"
FROM tblNoNameGiven;

You can then create a query based on the union query where you set a criteria under the [TheField] column.



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]