Mail Merge Report

[Jamie Steenberge]

I am looking to do a mail merge, and would love to keep this inside of
access.

I have a table with this information:

StudentID | LastName | FirstName | Grade | Subjects
103289 | Doe | Jane | 7 |
MathChr(9)43Chr(9)1,2,5Chr(13)Chr(10)ScienceChr(9)61Chr(9)3,4
64897 | Does | John | 6 |
ArtChr(9)43Chr(9)1,2,5Chr(13)Chr(10)HistoryChr(9)61Chr(9)3EnglishChr(9)64Chr(9)4,8
54678 | Doer | Jill | 8 |
EnglishChr(9)38Chr(9)1

I need to know two things.

1 - Each row should be on it's own page

2 - The Subjects column is tab delimited, and if there are multiple records
there are return characters at the end of the preceding lines. I could make
it whatever for the delimiters though. I need to be able to place the
Subjects column in a table on the report.

How can this be done?

Thanks in advance,

Jamie

 

[Jeff Boyce]

Jamie

If I'm interpreting your description correctly, you have a field named
[Subjects] that may contain multiple subjects.

If this is true, revisit your data structure. A basic tenant of good
database/table design is "one fact, one field". Trying to stuff multiple
facts (i.e., subjects) into a single field will make it MUCH more difficult
for both you and Access to work with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jamie Steenberge]

Jeff,

I have this as a create table.

This is the original structure that i have. I need to be able to pull one
report per week per student. So if there are three students this week that
have failing grades, and student one has two fails it will still only print
one report for student one but with both fails on it.

I have two tables that I need to create a mailing letter for. One of
the tables could have over 700 student records and the other table
could have 10,000 records a year depending on student grades.

Table 1

AutoID | StudentID | Last | First | Grade | YOG | SchoolYear
10001 | 9784 | Doe | Jane | 7 | 2013 | 08
10002 | 982745 | Doe | John | 6 | 2014 | 08
10003 | 26454565 | Doe | Jim | 8 | 2012 | 08
10004 | 234 | Doe | Bob | 7 | 2013 | 08
10006 | 234234 | Doe | Bill | 7 | 2013 | 08

Table 2

SubjectID | Subject | Average | CommentID | AutoIDf | EndOfWeek
24789 | Math | 59 | 1,4,2,5 | 10001 |
10/4/2008
24790 | Math | 43 | 1,4,2,5 | 10002 |
10/4/2008
24791 | Math | 22 | 1,4,2,5 | 10003 |
10/4/2008
24792 | PhysEd | 52 | 1,4,2,5 | 10001 |
10/4/2008
24793 | History | 61 | 1,4,2,5 | 10001 |
10/4/2008
24794 | Science| 32 | 5 | 10002 |
10/4/2008
24795 | Math | 49 | 4 | 10003 |
10/4/2008
24796 | Art | 58 | 1,5,3,4 | 10006 |
10/4/2008
24797 | English | 52 | 1, | 10006 |
10/4/2008

Now to the point. I want to make a table where the two are joined
like the following via a query though.

AutoID | StudentID | Grade | First | Last | Subject | Average |
CommentID | EndOfWeek

How can I go about doing this, as a single student could possibly have
10 failing grades in one week? I need to seperate it out this way so
I can push it into a word mailing to be mailed to the parents.

CommentID is not a typpo, it is just the way the dept requested it to
be.

Thanks in advance,

Jamie

 

[Jeff Boyce]

Jamie

I'm not sure I understand your data structure yet, so I'll simply offer a
couple observations...

Your original post seemed to imply that you could have multiple subjects in
a single field. That doesn't seem as likely with the description below, but
now it sounds like you can have multiple "comments" in a single field. If
this is true, you still have a data structure that needs more normalization
(remember, one fact - one field).

When you say "this is the original structure" and "I need to be able to pull
one report per week per student", I'm guessing you are trying to set up your
tables so they "fit" your reports. In MS Access, this is exactly backwards.
In MS Acccess, you design and build your tables to be well-normalized. Then
you use queries to assemble the data into sets you want to send to your
report(s).

What am I still missing???

Regards

Jeff Boyce
Microsoft Office/Access MVP