Query - user chooses fields to be selected

[herbtv]

Basically, the data I have is in monthly tables and I made the structures identical. But is has over 30 fields and each user might want something different, how do I set up a query to have the user select which fields they want returned? One user might want date, customer name and product, while another might want date, product and price.


Great forums by the way.

 

[Joe4]

Well, here are a few options:

1. Ask the users to design their own query (probably not desired)

2. Create a query with ALL the fileds, and show the users how to go into Design View and show the users how to Select/Deselect the Show option on each field.

3. Create a user form that lists all possible fields next to check boxes. Uers can check off the fields they want and then click on a command button to view the query. You would have to program VBA code behind that command button that will take all the checked fields and create an SQL statement to build the query. If you want to see what SWL code looks like, create a query in Design View and go to SQL View. This is what you want to build.

Option 3 is probably the most desired option, but it is the most complicated, as it requires some VBA code to build SQL statements.