Help Needed for Mail Merge From Several Access Tables

[MitchellGrainger]

Hi Guys and Gals,



I am pretty new to the using of Microsoft Access. Part of my role at work is to mantain a register of contacts (Aprox 2000 records). When moving into my role there was already an Access Database in place that had been designed and created by another member of staff prior to leaving.

The database contains a wealth of information, however this is spread across MULTIPLE tables (23 to be exact)

I am trying to create a word document that is basically a mail merge in order for me to drag the contact information over to print. However all the information I require is not in one table. Word will only extract the data from one source.

I have been trying now for 2 weeks to complete this task with absolutely no success. Is there anyone who can help me.?

I live and work in the West Midlands if there is anyone local that would spare me some time.

Mitchell

[June7]

I thought mail merge could use a query? Although think it has issue with calculated fields.

[MitchellGrainger]

Yeah my problem is creating the query. It's a nightmare trying to get the data I need.

[June7]

What is the problem? These tables involve several one-to-many or many-to-many relationships?

Might have to use VBA code to write data to a temp table and use that table as source for the mail merge.

[MitchellGrainger]

That's sounding like what it needs. However I haven't got a clue how to extract the data from the other tables into one temp table.

[June7]

Since I don't know your data structure, can't be specific. Methods could involve aggregate queries, INSERT SELECT queries, VBA code opening recordsets, etc.

[carpstar]

Have you tried creating a 'make table" query selecting the fields you need from each table and then running the mail merge from it?

[MitchellGrainger]

Hi there. Yes I've tried that. However when I add from a different table I loose data. Ie. I have 2000 data records. I take data from table 1. I still have 2000. I then take data from another and the it drops to 1800 records. If I start with the other table it works until I then go to another table.

[June7]

Why are you adding data from various tables in separate actions. The suggestion was to have a single query that pulled all the data together, do whatever calcs you need, then make a table from that query. Otherwise, manually create the 'temp' table with desired structure and run INSERT queries to fill the table with the desired data. Execute the queries manually or build code to run them at the click of a button.