Run Make-Table Query from Switchboard

[kalmi]

I think this question (or something similar) has been answered before so if I'm repeating I apologize. I am trying to run a make-table query from the switchboard. The goal is for the query to pull data from about 6 different tables, make a new table and then for that table to be used in a Word mail merge. So all I want is for the user to open the database, select the case number from a drop down list and then click a button to generate the table data for that case. The end user doesn't need to see the results of the query or of the new table. Any suggests on the best way to accomplish this?

 

[dbDamo]

Why do you need to make a new table? Why can't you use the query as the source for the mail merge and simply prompt the user for a case number when the query is run?

 

[dcb]

You say make a new table - Is the re a reason that it has to be new?
I know that the "Normalise" guys are going to fall over and twitch when they see this question as cant you use the query directly for the mail merge?

I see three ways here:
1) Use query for the merge
2) Dont make a new table everytime
Delete from a known table - SQL - DELETE FROM
Append to the newly cleaned table - SQL - INSERT INTO
3) Delete the entire table then append - SQL - DROP

It is possible to do what you propose - advisable, NO

Cheers

 

[kalmi]

Well I currently have it set up for the mail merge to pull data from a select query. But this means the person doing the mail merge has to open the query in design view and type in the case number under the criteria to get the correct dataset for the mail merge. I would like to get away from this process because not all users will be able to handle this, it really needs to be a select case number and click process in the database. But the mail merge function in MS Word seems to only be able to pull from a table or a standard select query. That's why I was leaning toward a make table query, the new table will be a static set of data that the mail merge will pull from. I'm open to other suggestions so if anyone has a better idea I'd appreciate it!

 

[Poppa Smurf]

Create a form for the user to select the case number from a combo box. Have button with the caption Mail Merge for the user to click. When clicked this will run your query etc. to do the mail merge.

 

[kalmi]

So the new form would be used to set the criteria in the query and save the query as well so that when I close the database and open the word document the correct data will show up. I guess that would work but I've never used a form to update query criteria, are there any tricks to it?

 

[dcb]

http://fisher.osu.edu/~muhanna_1/837/MSAccess/tutorials/param.pdf

There are many examples around - you should have a go at the microsoft tutorials
http://office.microsoft.com/en-gb/training/CR101582831033.aspx

 

[Poppa Smurf]

This is an example change the names that are in bold to your own names if you wish.

1. Create a new form
2. On the form add a Combo box and call it select_case_number
3. The record source for your combo will be a list of your case numbers.
4. Add a button to your form and call it run_query. Change the caption of the button to Merge Data or something similar. now in the Click event of this button run your query and other code as required.Use the Set warnings to turn the prompts off that appear before you run the query.
5. Save the form as frm_merge_data

Now in your query in the criteria of your case number field enter [forms]![frm_merge_data]![select_case_number], then save the query.