user selects columns to display in query

[cstickman]

Hey guys!!

Hi Everyone, first great forum! I have posted a lot and always get wonderful help, but as usual my users want to push me to do things I have no idea about. So the latest request was to have a form with columns listed and let the user select which columns they want to display in a query. Basically what is going to happen is we have a word document setup as a mail merge and we use the data from Access. Now the word document does not need everything in that table, but only a few columns depending on the letter. So I was thinking of designing a form with check boxes and let the user select which columns they need in the letter. The query will generate and move it over to Word to generate the mail merge. I reviewed a post doing a Google search that had VBA push the record source for the word document using a query. So I was going to incorporate that piece into it. If anyone else has any other suggestions that would be greatly appreciated. I would also like to use this same concept to let the users design a report by themselves. Right now I get so many requests to put together a quick query to extract data from the tables. That same concept would be nice to have a user go in and select the columns they want and then the criteria. I have not thought that all the way through yet, but something I would like to mess with later down the road.

Any help or suggestions would be greatly appreciated. Thanks!!

 

[plog]

Besides selecting columns, will users want to apply criteria? Will they want to determine sorting? Will they want to join more tables? Could they possibly want to get totals from the query?

If so, congratulations, you just reinvented the query design which already exists in Access. My advice would be to give power users a class on building their own queries in their own linked databases. You provide them with a database with all necessary tables linked to their version, possibly set up a few generic but commonly used queries for them to start off with, then let them build there own.

 

[cstickman]

Hi Plog,

We are currently doing that now with the super users. This is more for the users who have no idea what they are doing. I am more focused on getting the right query for the mail merge then letting users design custom queries.

 

[plog]

In that case I would build a MasterQuery with all the fields they could ever need. No need for the user to designate which fields to bring in--bring them all in.