Concatenating fields

[newfieskater]

I have 1 table with multiple fields

Unit First Name Last Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter Smith 2 Molly and Candy
200 Elaine Smith 2 Molly and Candy
300 Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake

I want to be able to print a report with all the pets and their owners but I dont want to have a specific pet listed more than once in the report.

I believe I need to concatenate but Im not so sure, I need help.

I would really like the Pet's Name column to be grouped so I can get a result such as the following.

Unit Full Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter, Elaine and Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake

[NeoPa]

I have 1 table with multiple fields

Unit First Name Last Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter Smith 2 Molly and Candy
200 Elaine Smith 2 Molly and Candy
300 Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake

I want to be able to print a report with all the pets and their owners but I dont want to have a specific pet listed more than once in the report.

I believe I need to concatenate but Im not so sure, I need help.

I would really like the Pet's Name column to be grouped so I can get a result such as the following.

Unit Full Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter, Elaine and Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake

You seem to want to handle this as a double many-to-many relationship.
Also, you're trying to force multiple items in an innapropriate record structure.
This is not good.

You should consider setting up separate tables for owners and pets.
However, concatenating multiple records into a single string is not easily supported.
You could produce results in a column if required.
Again, you have a real problem listing owners per unit AND pets per unit in the same result set.

I'm sorry that most of what I say is explaining what's wrong and what you CAN'T do.
Let's see what other contributors come up with.

[MMcCarthy]

The first problem you have is that you have one table where you actually need three.

tblOwner
Unit (PK - Assuming only one unit per owner and only one owner per unit)
FirstName
LastName

tblPet
PetID (PK and AutoNumber)
PetName

tblPet_Owner
Unit (PK)
PetID (PK)

The second thing is you can't do the report you are trying to do with one report. You would need to use a subReport to show Pet Information).

I have 1 table with multiple fields

Unit First Name Last Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter Smith 2 Molly and Candy
200 Elaine Smith 2 Molly and Candy
300 Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake

I want to be able to print a report with all the pets and their owners but I dont want to have a specific pet listed more than once in the report.

I believe I need to concatenate but Im not so sure, I need help.

I would really like the Pet's Name column to be grouped so I can get a result such as the following.

Unit Full Name # Pets Pet's Name
100 John Jackson 1 Oreo
200 Peter, Elaine and Susan Smith 2 Molly and Candy
400 Nedd White 1 Blake