sschnyd wrote:I have kept track of a fundraiser for our school on a spreadsheet for years, but would like to try doing the same thing on a database. In the database I have the following fields: Name, shirt size, locomotion type, # for breakfast, phone, teacher, grade, medical info, amount raised, prize certificate amount. What I want to be able to do is have it give me a total for each grade level, total raised for the whole school, and a formula that would determine the certificate amount. If they raise $50-$149.99 they get a $10 certificate, $150-249.99 they get a $20 certificate, etc. I can figure out a lot of this, but I am not very familar with the summary functions. Any suggestions, or do I just stay with a spreadsheet?
Steve
Since the only calculations involved seem to be sums and a calculation to determine the certificate amount, either the DB or the SS route will do the job.
[Edited after reading John's post]
John has given some good reasons for staying with the spreadsheet, and that may be a more comfortable route for you. My assumption is that you want to learn a few thngs about the database, and this is at least part of the incentive for the question. With that assumption, I took the DB tack.
[End edit]
Name should be a Name type field if you wish to sort by name, either overall or within classes. # for dinner can be a text field or a number field, depending whether this number is used in any calculations. Grade should be a number type field unless you are separating classes using a designation like 4A, 4B, etc. Except for Amount Raised, Grade Total, School Total and Certificate Earned, the rest should be text fields.
Amount Earned is a Number field, set to display as either Fixed (no currency sign) or Currency (same, but tacks on the currency sign chosen in your System Preferences), and set to a Precision of 2 (digits after the decimal)
Grade Total and School Total are both Summary type fields, and each has the same formula:
SUM('Amount Raised')
'Grade Total' must be placed in a Subsummary when sorted by 'Grade' part.
'School Total' must be placed in a Grand summary part.
Two fields are necessary, even though the formulas are identical, because AppleWorks will not allow you to put more than one copy of the same field onto a single layout.
Certificate Earned is also a Summary type field, with a somewhat more complicated formula.
In a spreadsheet, Certificate Earned could be handled with a with a Lookup table. In the database, the equivalent to a Lookup table is the CHOOSE() function. Both of these techniques require a predeclaration of the maximum amount to serve as the lookup key.
Certificate earned can also be handled with a function, which has the advantage that there's no upper limit to the calculated value.
If I've read the pattern correctly, each grade (or the whole school) earns $10 for a fifty dollar total, then another $10 for each 100 dollars beyond the first fifty.
We can simplify the formula (but get the same end result) by giving the grade an imaginary 'initial credit' of $50 raised, and changing the earning rate to $10 for every $100 raised.
The formula will total the amount raised, and add the $50 credit:
50+SUM('Amount Raised')
We want one certificate for each hundred dollars in the total, so the formula must divide this result by 100, then ignore the part of the result that comes after the decimal (ie. consider only the INTeger value).
INT((50+SUM('Amount Raised'))/100)
Finally, since each certificate is worth $10, the formula must multiply the number of certificates earned by 10 to calculate the amount.
10*INT((50+SUM('Amount Raised'))/100)
If the certificates are earned by each class, this field must be placed in the Subsummary part mentioned above. If the certificates are earned by the whole school, this field must be placed in the Grand summary part.
Summary parts will show on the screen only in Page view (see Window menu).
Subsummary parts willl show on the screen only in Page view, and only when the field named in the definition of the subsummary part has been used in a sort since the last data entry.
Regards,
Barry