jbullydawg Posted October 2, 2007 Share Posted October 2, 2007 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 More sharing options...
comment Posted October 2, 2007 Share Posted October 2, 2007 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. Link to comment Share on other sites More sharing options...
jbullydawg Posted October 2, 2007 Author Share Posted October 2, 2007 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 More sharing options...
comment Posted October 2, 2007 Share Posted October 2, 2007 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. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6185 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 accountSign in
Already have an account? Sign in here.
Sign In Now