Interesting Report Request

[jbullydawg]

In our non-credit database we track programs and registrants. Pretty straightforward. In our registrant table we track the date they registered and have 3 fields for payments (Payment 1, Payment 2, Payment 3) and the date of the payment (Date Rec. 1, Date Rec. 2, and Date Rec. 3). Rarely does any registrant make more than one payment but it has been known to happen.

So here was the request made of me:

Build a report that counts all of the registrants in each program that registered in a certain timeframe as well as provide the total payments made during that same timeframe. Now, I can search through the 3 payment dates and isolate only those records that have payments in that timeframe.

My problem is that I don’t know how to get a total of only those payments made during the given timeframe.

For example:

Payment 1 – 8/31/2007, $200

Payment 2 – 9/1/2007, $200

Payment 3 – 9/3/2007, $200

If the user wants the report to only reflect those payments made between 9/1/2007 and 9/30/2007 this registrant’s contribution would only be $400. However, the report totals $600 for this student toward the course's total for all its students.

So, here’s what I have done so far:

[color:red]Show Custom Dialog (asks for gStartDate and gEndDate)

Enter Find Mode

Set Field [Registrant::gSearchText; Registrant::gStartDate&”...”&gEndDate]

Set Field [Registrant::Date Rec 1; Registrant::gSearchText]

New Record/Request

Set Field [Registrant::Date Rec 2; Registrant::gSearchText]

New Record/Request

Set Field [Registrant::Date Rec 3; Registrant::gSearchText]

Perform Find [ ]

Go To Layout [“Course Contributions” (Registrants)]

Sort Records (blah, blah, blah)

Enter Preview Mode

The report has the following fields:

Course No., Course Name, CountRegistrants, SummaryTotalPayments

This accurately identifies the records with payments made in the given timeframe and gives me the count of registrants for each course. However, I have no idea how to total only those payments in the given timeframe for each course.

PLEASE HELP, this is driving me crazy!!!

[comment]

This is a classic example of why Field1, Field2, Field3, etc. are bad data structure. Move your payments to a related table (where each payment is a separate record) and your problem becomes trivial.

[jbullydawg]

I would love to build the database that way but the primary identifier is a problem for us:

1. We can't keep SSNs on file.

2. Alot of our registrants are good 'ole boys and they don't have email addresses.

3. some of our programs only require name and phone number for registration.

It's a hodge-podge of information gathering. I've asked in the past if we could make email addresses mandatory for purposes of a primary key but I was laughed at and given the same explanation as above. Come to find out, it was true...most of our registrants for out non-credit courses are 'in the field guys' and don't worry themselves with email.

That's why this report is so hard.

[comment]

Use a auto-entered serial number as the primary key in the registrants table. That is the preferred method anyway, with SSN's and/or e-mails or without.