Table Use Question

[tmozer]

Using Crystal Reports 8.5 and Oracle tables, I am very weak in the linking of the tables I am using. I have been told to use "Outer Left Joins" on all my links.

Most of my results involve the use of what three "master" tables. LabCase, LabExam and LabRept. A record is created in LabExam each time an assignment is created. The record is copied to LabRept (same structure as LabExam) each time an assignment is completed. So LabExam has only open assignments and LabRept has only closed assignments. Assignments come from cases. When a case is received, a record is created in LabCase. This is the "main" master table since there is only one record for each case. Each case can have many assignments. Each of these three tables have a field called Case Key, which is how I link them. Up until now I link LabCase to LabExam and/or LabRept (left to right). The reason is that I am interested in assignments, either open or completed. However, sometimes we want to look at assignment data based on cases. Therefore we do not want to count multiple assignments.

Should I try linking (L-R) LabExam/LabRept to LabCase??

 

[tmozer]

I gave it a shot.... All I have is a headache.

Basically I need a report that will total unique assignments pending 30 days or less and those pending more than 30 day for four locations and four sections. Easy except for the "unique" part. A unique assignment would be one per case per section per location. And it would be the assignment pending longest (first created). So, if I sort by Case Number, location and section, I need to ignore the dups and only count the first assignment created and then do a grouping or cross tab that would count the under and over 30 day unique assignments......

I don't have a clue how to start!

Again I am using CR 8.5 and Oracle tables.

 

[Madawc]

Take a look at running totals. This includes a distinct count option; i.e. you only count when the value changes.

There is also an 'evaluation' section, which can be for each record, or a group, or based on a formula.

If you use running totals, they are working out at the same time as the Crystal report formats the line. This means you can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.
If you use summary totals, they are taken directly from the data and can be shown in the header. They can also be used to sort groups, or to suppress them.
To get a running total, right click and choose Insert and Running Total. If you want to get the summary totals for a database field, choose Summary instead.

Regarding Left Outer, the idea is that Record A will sometimes have one or more associated examples of Record B, but also sometimes not.
If I've understood you correclty, a LabCase might have associated records in either LabExam or LabRept, or both. So I'd start from LabCase. But Crystal is flexible and you can often get the data you want from several different data structures.
It is often a good idea to just start, see what you get and change it if it does not suit you.

Madawc Williams (East Anglia, UK)