Access Query Data Source Question

[Han]

Using an Access query as my mail merge data source, I would like to return
multiple names from the same table as merge fields.

My "Contacts" table looks like this:

ContactID, FirstName, LastName

I need to return the FirstName and LastName for each ContactID.

SELECT [Contacts].[FirstName] AS ClientFirstName, [Contacts].[LastName] AS
ClientLastName, [Contacts].[FirstName] AS DoctorFirstName,
[Contacts].[LastName] AS DoctorLastName, [Contacts].[FirstName] AS
NurseFirstName, [Contacts].[LastName] AS NurseLastName, FROM Contacts

How do you write the WHERE clause to distinguish each FirstName and LastName
using the ContactID for each? The goal here is the have a nice
mail merge data source containing all the contact names.

Is this possible or is there a different way to accomplish this?

Thanks,
Han

 

[Peter Jamieson]

This is really an Access (or SQL) question, so you may find it simpler to
ask elsewhere, but...

Can we assume that each patient, doctor and nurse is in the Contacts table?

If so, how do you know which doctor and nurse is associated with each
patient? And is there really only one of each per patient, or what?

Typically you would need either "foreign keys" in your contact table, e.g.

ContactID, FirstName, LastName, DoctorContactID, NurseContactID

or for multiple relationships, you might use a "link table", e.g.

PatientContactID, CarerType,CarerContactID