Displaying multilpe records 1

[SHJ68]

I am trying to write a report that will group a variety of data under a particular person. My plan is to set it up with all information grouped under participant name. Some examples of information I need under that name are birthday, disabilities, staff working with that person etc. The problem I keep running into is that I only get one disability or one staff person under the participant name. All of the information repeats itself as a new group instead of listing out everything under the name one time. Any ideas on how to get around this? I'm sort of new to Crystal and really need help.

 

[HowardHammerman]

The big question is: "how is your data organized?" Is there is one table with one record per employee and associated tables with multiple records per attribute (such as disablity)? If so, group on the employee ID number and link to the other tables on ID number using a Left Outer join type.

Depending on how you are set up, you may have to use sub-reports that are linked to the main report on the ID number.

We need more info. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[SHJ68]

I'll try to better explain. I have a consumer table with consumer ID being used to link the disability table, disciplinary team table, employment table, consumer status, and programs tables. Each of these tables could feasibly contain multiple records for one consumer. I have tried to use the left outer join for all of it and so far this is what it looks like

GROUP 1 consumer name birthdate
address 1
address 2
city, state zip

GROUP 2 disciplinary team
disciplinary team
etc.

Now I need to add disability, and employment. The problem is they could also have multiple records to one consumer.

I hope this explanation makes more sense. I appreciate any feedback. I'm kinda shootin' blind right now.

Thanks in advance.
Sigried

 

[IdoMillet]

Sigried, this clearly calls for a subreports approach:

1. Create a Main report that simply lists the Consumers in the CONSUMER table. Group the report by Consumer_ID assuming you want a header and footer for each Consumer information.


2. Assuming you need five "data areas" for each Consumer
(disability, disciplinary, employment, status, and programs), create 5 Detail sections (You would need to Right Click the Detail section and "Insert Section Below" 4 times).

3. In each of the 5 Detail sections, insert a different Linked subreport that accepts the Consumer_ID and provides one of the five "data areas" for that Consumer.

Cheers,
- Ido
CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[HowardHammerman]

The sub-report is the way to go. Howard Hammerman,
Editor and Publisher of Database Reporting Made Easy newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[SHJ68]

First of all I want to thank both of you for your help. You were right, subreports were definetly the way to go. Now however I end up with everything multiple times. Ack!! I'm so sorry for bugging ya'll with ignorant questions, but what do I do now?

I truly appreciate the assistance. I'm making notes on everything so I can learn.

Sigried

 

[IdoMillet]

Right click the subreport, select Change Subreport Links, and specify Consumer_ID as the link. The subreports should then have a filter condition limiting their records to the Consumer_ID group within which they are showing.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[SHJ68]

Ido,

Thanks for the information. By sheer mishap I had already done what you suggested. I'm beginning to think the problem is with my joins. On some clients I get just the information I requested while on others I get the information I requested 6, 7, or 9 times. Where that number comes from is beyond me. The only thing I had considered might be part of the problem was that in one subreport I used a table view (one table that consists of several already linked tables). So, I went and changed everything to be strictly individual tables instead of views. I'm still getting the same results.

Perhaps my problem is in understanding the joins. I'll have to research that some more. If you have other ideas, I'd be happy to hear them. Ya'll have already helped me SO very much.

Thanks
Sigried

 

[IdoMillet]

If a subreport is showing you duplicate info, you must be joining to a table that has many matching records for each of the information pieces you are interested in.

For example, in a subreport listing "Disabilities" per person, you should NOT join also to the "Staff working with this Person" because if that person has two staff members working with him/her, each Disability would be repeated twice (once for each Staff member).

In short, make sure you are NOT joining to tables you don't need.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com