Help: list box form to select fields for results table?

[HelenTD]

I need advice on how to approach what should be a straightforward Access form. I know how to do this for myself as a query, but I'd like to make a form for others to use, and I'm not familiar with forms. Most of my Access experience is in queries / SQL, not forms. I have done VBA programming for Word and XLS.

Is the following a simple thing to do? What search terms should I use to look up how to do this here or on Usenet?

Simply put, I need to:
1. Populate a list box with the fields from 3 tables so the user can select 2 or more fields from 1 or more tables.
2. based on which fields the user selects, run a query that selects those fields into one results table. It will either be a simple SELECT columns or SELECT columns with a FROM... JOIN, because 1-3 tables could be used.

The user's goal is to make a pivot table in Excel based on data exported from Access. In Access, the user sees a form that lists the 40 available fields from 3 tables. The user selects 2 or more desired fields, presses a button, and a results table is created with those fields. The user then exports the results table. Seems simple, but I'm not quite sure how to start.

The relevant 3 tables in the database are
1. Customer (CustomerID and then 9 customer characteristics such as age, sex, city... i.e. Customer.Age, Customer.Sex...),
2. Survey_Answers ( SurveyID, CustomerID, Answer1, Answer2...)
3. Survey_Characteristics (SurveyID, SurveyFilledOut, SurveyType, SurveyDate, ...)
Not every customer got a survey, and not every survey was filled out.

The list at its simplest would just be populated with the 40 fields from the 3 tables. How do I look up how to populate the list with the field names? I recently bought Litwin/Getz/Gunderloy's "Access 2002 D.D.H." From what I read I'd need a form with a list box, and the list box must allow multiselect. Is this right?

When the user hits "make table" on the form, the code that runs would just need to be SQL populated with the fields / tables the user selected. What are the search terms for how one get the selections the user made back into a SQL statement?
Thanks.

 

[Parra]

This is not complex but its difficult to write down.

Create a query that will list the option in the drop down box.
Create a query that has the fields for exporting.
Create a form. In the design view add a drop down box and follow the wizard and choose the query you created for the options for the drop down box.
Go back to the 2nd query
In the field where the option of the drop down box will go add the following in the criteria
[forms]![FrmReports]![combo15]
OK FrmReport: enter the name of your form
combo15: enter the name of the dropdown list of the form.

Create a report based on the query.
Go back to the form and add a command button to run the report.

So the user will choose and option from the drop down box and then click on the button to run the report and the data will be extracted.

To export it to excel, in the report view there is a little button on the toolbar that has a word or excel export option. Tell them to click on it.

I know this might be a bit confusing, but it's really simple.