Complex report - Aliases and possible subquery? 1

[neilmcdonald]

Hi,

I need to write a report in Crystal 8.5 to read data from a SQL server.

The problems are:
a) I cannot see a way to use a parameter to restrict the data
b) I cannot format the data in columns

There are four tables as follows:

Contacts: ContactID, Name
Event_Attendees: ContactID, EventID, Status
Events: EventID, EventDescID, StartDate, EventCode, Subname
Event_Desc: EventDescID, Description

The database stores details of events and attendees.

A complete event is divided into 5 modules. An entry is created for each module, with a Subname of Module x, and the 5 modules share a common EventCode.

My problems arise as a person may not pass a given module and would therefore be transferred to the same module on the next available course with the same Event.Description. This creates more than 1 record for each attendee per module.

I would like a report grouped by Event.Description, ContactID and Events.Subname.

In the ContactID footer, I would like a column for each Event.Subname showing the last record (by Event.StartDate), EventCode and Event_Attendees.Status.

I've looked at several answers regarding subqueries, and I think that might be the way to go, but my SQL experience is very limited.

As if that isn't enough, I also need to be able to specify a parameter which will show only those people who are attending a given EventCode, but including all other event records for those people. I have done this before using aliases, but I no longer have access to that report and can't remember how it's done.

I've got as far as grouping the data as above, sorting by date, and inserting the relevenat fields into the event.subname footer to give the last records. Although not ideal, this may be enough if I can sort out the parameter issue.

Apologies for the long question - I would be very grateful if anyone can help me with either of these issues.

Thanks,

Neil

 

[hilfy]

To handle the parameter issue, make Events your master table and link it to Event_Attendees. Then link to a another copy of Event_Attendees on ContactID and from there to another copy of Events on EventID. Use the parameter to filter the original Events on the event ID and make sure to turn on "Select Distinct Records".

It would look something like this:

Events ------> Event_Attendees --------> Event_Attendees1 --------> Events
      (eventID)               (contactID)                (contactID)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[neilmcdonald]

Thanks hilfy - I appreciate your help. That seems to be working for me. If you've got any links or sites that explain the logic behind this, I'd love to read up a bit to increase my understanding...

 

[hilfy]

Here's the basic logic:

1. Use the Events "master table" to filter on just the event you're looking for.

2. Link on EventID to the first copy of Event_Attendees to find all of the attendees for that event.

3. Link on ContactID to the second copy of Event_Attendees to find all of the events that the users in step 2 have attended.

4. Link on EventID to the second copy of Events to find the details about the events from step 3.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.