One to many relationships

[jmargie]

I have a 1-m table relationship where an individual can have multiple designations. For instance, Joe Smith can be an ASA, FSA, MIID and DAS while Mary Jones is an ASA, FSA, and MPD. Each designation is part of a 1 to many relationship in a different table than the individuals name. I am trying to create a directory for each individual that lists all their designations. Currently, I am getting one record for each designation, so I have duplicate member names. Is there a way to get this information without creating a record for each designation instance?

 

[rhinok]

Try grouping by Individual

 

[kenhamady]

You will always get multiple records per person, but you don't have to print them. what do you want the output to look like? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[jmargie]

I have 2 tables, one with an persons name and another table with one to many designations they have with the year the designation was obtained. The output is coming out as follows:

Smith, John (AAA 1999)
Smith, John (BBB 2000)

I need the format to be as follows:

Smith, John (AAA 1999, BBB 2000)

There is additional information such as address, phone, etc, but this info is easy to obtain. Is there a way to do this?

 

[kenhamady]

This can be done, but only if the string of values will never exceed 254 characters.

It requires using Crystal variables in a pretty sophisticated way. Are you familiar with CR Variables? Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[databaseman]

Perhaps a simpler way to hande this is by grouping on the individual's name and then having the designations and the information pertaining to the designation on the detail line. Thus you get this:

John Smith
ASA 1998
FSA 1999
MIID 2000
DAS 1999

Mary Jones
ASA 1999
FSA 2001
MPD 2001

Is this appropriate to your report? I hope that this helps.

Databaseman Database Man
Defender of Life, Liberty and the pursuit of accurate database reporting! Follow my free tek-tip adventures at:

http://www.hammerman.com/dbman.html