Merging Data from a query


I have a database (MS Access 2003), until recently I had 1 table with pupil names and addresses which I merged with MS Word 2003 and all worked fine. I have now had to split the table as pupils can have more than one Parent at different addresses. So, I have a 1-many relationship between pupil and address table.

I have created a query to bring all the data together but now when I try and merge it I only get 1 record rather than 180 as there should be.

I have searched loads of web sites tried all sorts of connotations of queries and word docs.  Any suggestions appreciated.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Barry CunneyCommented:
As a once off make the 'created query to bring all the data together' a Make Table query to get a table with the required format.
Then change this query to an Append Query for future runs.
Make the newly created table the source for the mail merge.

So the going forward you will need 2 steps(which can possibly be automated with VBA)
1. Clear out the table with merge address details
2. Run Append query to populate above table.

The do the merge
That would be a one-to many query. Word's Mail merge needs a one-to-one query. I have listed some alternatives here.

If you want help or further assistance with any of the workarounds, let us know.
tmckeatingAuthor Commented:
Struggling to understand any of the solutions maybe the best solution is not to use Word and just design reports like the main documents?
If you don't need the extra features that Word provides (formatting, merging to email etc), then the Access report facility provides a very good alternative.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.