Mail merging multiple fields from single lookup table into word via query

  • Thread starter Thread starter Simon Bloor
  • Start date Start date
S

Simon Bloor

Dear all

Any help on this would be much appreciated...

I have a table taking information from a single lookup
table tblSupportWorkers into several fields SupportWker1,
SupportWker2, SupportWker3 etc etc in a student record
tblStudentRecord, specifying the names of the support
workers linked to a student - ie sometimes there may be
none, sometimes 1, sometimes 3 etc - but all drawn from
same pool of support workers - hence the single lookup
table.

I would like to be able to send this information into Word
but understand that if a mail merge is done with the data
in the student record table the primary keys of the
support workers will be sent rather than support workers
names...and so I need a query which specifies the source
of the data to be the tblSupportWorkers rather than
tblStudentRecord - but...and heres the problem...

I can't see how I might specify the source as
tblSupportWorker several times in the query - so as to
pull in that information SupportWker1, SupportWker2,
SupportWker3 in the tblStudentRecord...

Perhaps theres a way of further refining the query through
the use of criteria...? in order that the infomation drawn
in against several fields but from the same source can be
shown in the query and thus available as "real"
information for mail merging?

Regards

Simon
 
Simon

If your table has multiple "repeating" fields ("SupportWker1",
"SupportWker2", ...) you have committed spreadsheet on Access. While this
type of data structure is the only way you can handle this in Excel, Access
is a relational database ... as John Vinson (in tablesdbdesign) regularly
reminds folks, "use it relationally!".

If you convert your table structure to reflect the "many-to-many"
relationship among your entities (just guessing, but don't you have Events,
Persons, and SupportWalkers?), you'd end up with something like:

tblEvent
EventID
... (other attributes of this Event, whatever that is)

tblPerson
PersonID
... (other attributes of persons, e.g., FName, LName,
DeliveryAddress, City, PhoneNumber, ...)

trelSupportWalker
SupportWalkerID
EventID (which one of the Events is the person supporting)
PersonID (which person is supporting this particular Event)
... (other attributes of this association, e.g., on what date,
weather conditions, ...)

NOTE: I've suggested putting the date in this third table, since I could
imagine a recurring event (each year), using the same name, but happening
different dates. With the above structure, you could find anyone who had
ever participated in the event, and only those who participated in the event
on a specific date.

Creating a query from THIS type of structure makes creating a label set (and
exporting to Word, if necessary) quite easy. A join of these three tables
provides Person name/address/whatever for a selected Event.
 
Back
Top