By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,399 Members | 1,028 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,399 IT Pros & Developers. It's quick & easy.

Concatenating fields

P: 1
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
Nov 24 '06 #1
Share this Question
Share on Google+
2 Replies


NeoPa
Expert Mod 15k+
P: 31,661
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.
Nov 24 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 25 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.