Jump to content

Interesting Report Request

This topic is 6099 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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!!!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 6099 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.