MS Access output options

March 5, 2008 5:36 PM

How can I mail-merge data from MS Access to Word from two different database tables?

I have an Access database (note: I don't get to choose my software, so it has to be MS Office) to keep written examination data. One of my tables is a table of questions. Each question has a one-to-many relationship with a table of answers (so I can grade each part of the answer on it's own)

Currently to make an exam, I use an access report. That works, but I'd like to try it in Word also. Problem is, I can't seem to pull data out of Access from multiple linked tables. The only thing I can think of is to make a query in access that has the question data and the answer data in one flat table, then mail-merge that into Word. Problem with that is, I get

question1 answer1.1
question1 answer1.2
question1 answer1.3 etc, when what I want is

question1
answer1.1
answer1.2
answer1.3

Is there a semi-simple way to do that (meaning, without writing crazy VB code,) or am I stuck with the reports in Access? I'd like to automate it as much as possible, so my training guy can select the exam he wants to print from the exam table and just press a 'make exam' command button and magically get a word file for the exam and a word file for the answer key. If it would be a cumbersome process for him, I'll just keep it like it is.

posted by ctmf to Computers & Internet (6 answers total)

In Microsoft Access 2003, you can convert reports into Word. I have a button on my tool bar that looks like the W of Word - click on that and instant word doc.
posted by b33j at 6:11 PM on March 5, 2008

With the export report to Word feature, it makes a snapshot of the report that I already had, which is ok I guess, but at that point I may as well just print the report. I should have said, the point of using Word would be to avoid having to screw with the report design form to change things like margins, tab stops, etc. when someone decides that the standard exam ought to look like this instead of like that. I'd rather have it be a simple matter of changing a page or paragraph style in Word, which more people know how to do.
posted by ctmf at 7:01 PM on March 5, 2008

Okay, gotcha. I'll think some more.
posted by b33j at 7:12 PM on March 5, 2008

How about this:
Put dummy answer# 0s in the answer table, with the answer value null. Then when you do the join query create a calculated field that only shows the question if the answer # is 0.
Then when you do the mail merge you'll get

question1[blank]
[blank]answer1.1
[blank]answer2.2

I don't think I'm explaining that very well...
posted by yarrow at 7:34 PM on March 5, 2008

I think yarrow is on the right track.

1. For each question, create an answer that uses a consistent placeholder value.
2. Create a query with 3 columns: Question | Answer | Combined
3. For the "Combined" column, write a simple if/then statement that looks at the value in the "Answer" column and if it's equal to your placeholder text, use the value of the "Question" column, otherwise use the value in the "Answer" column.
4. Do your mail merge on the "Combined" column
posted by junesix at 12:46 PM on March 6, 2008

Yeah, that's a good idea - after fiddling with it some more today at work in my spare moments, I can see how the placeholder approach could work. After a lot more fiddling. Aesthetically, I don't like that solution, though, because I'm going to have to let this thing back loose on other people that are not that computer- or database-savvy. I'd like everything in it to be as straightforward as possible and not rely on any tribal knowledge trickery. That is to say, the person who adds questions should not have to know to add placeholder values, etc.

I'm thinking now of some sort of Access macro that makes separate Word files for each set of answers, and then compiles that in with a master Word document made with the questions. I'll have to look into how hard that would be to do (and if it would even work,) though. I've done some (very minor) VBA stuff in the past, and it would be worth it if it would make the final product fairly self-explanatory to use, but I don't think my skills are up to anything too crazy.

Best would be if there was some way in the mail merge to say
[if this is the first time you've seen this question data <>> otherwise don't]
posted by ctmf at 4:10 PM on March 6, 2008