queries and labels

[Dorothy]

I have two tables, one with resident information (names, birthdate, etc) and
then another table for emergency contact info for each resident. The resident
PK is a foreign key in the emergency contact table. In the emergency contact
table multiple emergency contacts for each resident are listed in a different
record (row) rather than one record with field names such as "contact name 1"
"contact 1 phone" etc. I wanted to print labels of the resident name and a
list of all emergency contacts on one label. I created a query for this but
unfortunately if a resident has lets say two emergency contacts two separate
records appear in in the query and therefore two labels - so the first label
shows "Jane doe" and one emergency contact and then the next label shows
"jane doe" and her other emergency contact. Have I really messed up in
setting up these tabels or is there someway around this where "jane doe"
shows up on one label with a list of all emergency contacts also on that one
label? Do I need to redo the emergency contact table to show fields as
mentioned above - Contact 1 name, contact 1name, etc. I hope this is clear.
Thank you.

Dorothy

 

[Tom Wickerath]

Hi Dorothy,

Your table design sounds correct. You definately do *NOT* want to use
repeating groups of fields, as in "contact name 1", "contact 1 phone",
"contact name 2", "contact 2 phone", etc.

I believe you are going to need to concatenate the many side records into a
string. Try the following example in the Northwind sample database. Then see
if you can apply something similar to your label report:

How to Concatenate Data from the 'Many' Side of a Relationship
http://support.microsoft.com/?id=210163

You might try including a VBCRLF in the concatenated string, to force a new
line when you feed this string to a textbox in the label report. Also, I
assume there is some reasonable limit as to how many emergency contacts each
resident can have. Otherwise, you might have trouble fitting all of the
information on a reasonably sized label.

Tom
______________________________________

:

I have two tables, one with resident information (names, birthdate, etc) and
then another table for emergency contact info for each resident. The resident
PK is a foreign key in the emergency contact table. In the emergency contact
table multiple emergency contacts for each resident are listed in a different
record (row) rather than one record with field names such as "contact name 1"
"contact 1 phone" etc. I wanted to print labels of the resident name and a
list of all emergency contacts on one label. I created a query for this but
unfortunately if a resident has lets say two emergency contacts two separate
records appear in in the query and therefore two labels - so the first label
shows "Jane doe" and one emergency contact and then the next label shows
"jane doe" and her other emergency contact. Have I really messed up in
setting up these tabels or is there someway around this where "jane doe"
shows up on one label with a list of all emergency contacts also on that one
label? Do I need to redo the emergency contact table to show fields as
mentioned above - Contact 1 name, contact 1name, etc. I hope this is clear.
Thank you.

Dorothy