Hi

I've been tasked with analysising the attendance trends for a series of events that the University runs.

I need to establish:

Repeat attendance and crucially which event was the first one the alum attended.

For each event what was the percentage of repeat visitors as opposed to first time.

For each event how many people have not attended any subsequent events.

My initial thought was this would be more acheivable in Crystal (hence, posting it here!) but if it would be easier in Excel or Access then please advise.

Thanks in advance

Peter

First, why do people insist on verbifying nouns when there is already a perfectly good verb available.  And why do people insist on using the awkward structure of "I have been tasked with".  What's wrong with "I need to analyze/analyse..."?

On to your question.  Crystal is not the right tool for this job.  The question requires doing a grouping that is different from the one required for the summary and Crystal can't do that.  You are better off doing this directly in SQL or in Access.

Create a query grouped on the constituent and calculate the first event by using Minimum on an appropriate field (such as the event date), a (distinct) count on the same field, and a maximum on the same field.  This will immediately give you their first event and their total number of events.  You can also figure out people who have only attended one event, because their min will equal their max.  (You could do this much in Crystal, but since you need to recreate this for the next part, it doesn't make much sense to duplicate your work.)

For each event, you'll want to join the participant records to the previous query on the constituent key.  First-time attendees will have the current event date equal to the minimum event date; repeat attendees will have the current date greater than the first; and people who have not attended a subsequent event will have the current date equal to their maximum date.

Drew

Many thanks for the solution - and the free english lesson.

Peter