Get two detail tables to show correctly in detail section 1

[tofuTnT]

Hi All,

Can anyone take a look at the following report:

http://home.earthlink.net/~song.lin/crystal.rpt

http://home.earthlink.net/~song.lin/db1.mdb

and tell me how I can get the detail section to show correctly? That is to show only one copy of the dates and one copy of the degrees for each person.
something like
11/8/2000 BS
3/1/2000 Honorary Degree
10/5/1999
6/7/1998
4/20/1998


The table structure of the mdb file was kind of weird, that is why I decide to upload the sample file instead of explaining it.

Thanks in advance
Song

 

[tofuTnT]

It has been two days, and no one could help me on this?
Maybe I shouldn't post question like this? by postin link to the sample report?
If that is inappropriate, please let me know. I really need to solve this problem.

Thanks a lot!!!
Song

 

[Ngolem]

show us the structure problem...and describe what you have done to try to solve it.

You don't describe the version of CR you are using.

Much better to just give us the relevant fields in the 2 tables and describe why you think there is a problem

Read my tagline...

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[FVTrainer]

Well, I downloaded your report and there is not enough information in your post to act on your request. You've given a sample of what you want, but it doesn't address what is wrong with your data.

You have links in the form:

A to B
A to C
A to D
D to E

All the joins are inner joins, except the D to E join, which is a left outer join. Please provide info on whether the relationship between each of the tables is one-to-many or one-to-one.

Additionally, in your report you have:

Person 1
date 1 degree
date 2 (blank)
date 3 (blank)

Person 2
date 1 degree 1
degree 2
date 2 degree 1 (the same degree 1 as in the date 1 record)
degree 2 (the same degree 2 as in the date 1 record)
date 3 degree 1 (the same as above)
degree 2 (the same as above)

For the 1st person on the report, why are there dates listed without any degree associated with them? What do those records mean? Why does the 2nd person have two degrees (the same two) for each date?

How to address this problem is going to be based on an understanding of the underlying data, and even though you provided the database, that in itself doesn't provide a clear understanding of what the data means. And while I understand the implications of a left outer join, I can't assume a field from a table joined with a left outer join is null because there is not match in the linking table. So a bit more info would be good.

 

[tofuTnT]

Hi FvTrainer,

Thanks for the reply.

Cn -> CnBio is one to one
Cn -> CnAct_2 is one to one
Cn -> CnAct_1 is one to many
Cn -> CnRel is one to many
CnRel -> CnRelEdu_1 is one to one.

CnBio has the bio info of a person (name in the sample table).
CnAct_2 contains the attributes of that person (Solicator, Status)
CnAct_1 contains the actions for that person. There might be 0 to many actions.
CnRel contains the relation info of that person, the detail is in CnRelEdu_1 tables, which contains degree info, class of, school name.

Basically what I want is two columns. The first column has the Action Date info, there might be 0 or many of them. The second column will have their education info, again, there might be 0 or many of them.
There is no relationship between the action date and the education info.

Person 1
Action_Date_1 Degree_1
Action_Date_2 Degree_2
(might be more action (might be more
or no action or no degree
at all) at all)

I have tried group by person name, then put action_date in detail. and put degree info in a subreport and put it in the GH. I got what I wanted, but it took a LONG time to refresh a report. so i like to have a simple way of doing this.

I hope i have made myself clear, if not, please let me know.

Thanks!
Song

 

[lbass]

Here's something that will work--I just tested it on your report.

Insert a group header #3b. Select the degree field and insert a summary on it at the group level, choosing "NthMostFrequent" as the summary field, with N=1. Then insert the same summary on it multiple times, increasing N by 1 each time, up to the maximum number of degrees that will be displayed. Drag these summaries into the Group Header #3b. Then delete the degree field from the details section.

Then go to format section->group header #3b and check "underlay following sections." Then go to details and check "suppress blank section."

-LB

 

[tofuTnT]

LB,

Thanks so much. That is exactly what I am looking for.

Song