One table query giving different results

[fjcunninghamjr]

Hi,
Trying to create a query base on one table (Insurance Companies) in the table are four fields (Auto, Homeowners, Life, Renters). I know I'll able to select just one of these fields (Auto) to produce the desired result.
My problem is I would like to use the same query and produce results on another field, say, Homeowners and so one. So, when I get to do a report, the report will prompt me for the field I wants for the results.
Thanks for any help.
Frank

[June7]

Fields referenced in query object cannot be dynamic. Field is included or it isn't. Cannot dynamically change unless you have VBA procedure to modify object via QueryDefs.

Consider 'normalizing' with a related table where each insurance type is a record instead of a field. Then you filter for whatever insurance type you want. Or a UNION query can rearrange your current structure to a 'normalized' output that can be filtered like table.

Otherwise, an IIf or Switch or Choose expression in query or textbox can return desired field value based on parameter. This calculated field would be ugly but doable.

[Micron]

in the table are four fields (Auto, Homeowners, Life, Renters)

I also figure the db is not normalized based on that statement. You probably designed it the same way you create spreadsheets because that's what you're used to. If you fix that problem now, a lot of issues like this will go away or never get raised in the first place. What you want to do can be accomplished by some kind of work-around (like making a choice on a form control that dictates which query thus which field to use for a report) but the idea is to eliminate the work arounds as much as possible. Using a UNION query is also an option, but I see that as needing another query to filter the 'normalized' results that it would create from un-normalized data, and all of that can be prevented from the get-go.