Page 1 of 1

Database

PostPosted: Thu Mar 16, 2006 9:51 pm
by sschnyd
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

Re: Database

PostPosted: Thu Mar 16, 2006 11:21 pm
by haumann
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


It can be done, but...

The database idea has a number of detractors as I see it. You're already at ease with the spreadsheet version. Unless there's some compelling reason you haven't mentioned (like generating a lot of differently formatted reports), I'd stick with what works. A spreadsheet is a lot easier to debug than a database with imbedded formulas and dependencies.

I hope I haven't dampened your enthusiasm.

[email protected]

Re: Database

PostPosted: Thu Mar 16, 2006 11:45 pm
by Barry
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

Re: Database

PostPosted: Fri Mar 17, 2006 12:07 am
by haumann
Barry wrote: ... snip ::: 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. ::: snip:::


Barry,

The same thought crossed my mind. I just thought this might be an awfully ambitious DB introduction. None-the-less, you've given the project a good start.

Now, where is Lucas?

[email protected]

Re: Database

PostPosted: Sun Apr 23, 2006 5:35 pm
by Jorge Lucas
haumann wrote:
Barry wrote: ... snip ::: 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. ::: snip:::


Barry,

The same thought crossed my mind. I just thought this might be an awfully ambitious DB introduction. None-the-less, you've given the project a good start.

Now, where is Lucas?

[email protected]


John,

I was in vacation and with no Internet access at home for almost a month.

Besides that, there are two reasons for my absence in the last times:
1) when I see some topic regarding SS and DB, almost always someone answered it already and...
2) I am a little disappointed with some posts I wrote and got no return messages saying that they worked or not, specially when the asker was a guest or a first-time poster.

My purposes in trying to give a solution for a problem that was not mine at first are: help those who needs help and improve my ability in solving problems.

But this requires time and is so frustrated when you feel you spent that time for a stranger that did not cares about it.

I got people that gave me real important answers, in the forums, and I always gave them my thank you or I asked for complementary explanations.

There were times when I sent my troubled files to you, Barry and Yvan (the AppleScript expert) and I am sure that you felt happy in giving me a help.

This topic starter (first-time poster) did not return Barry´s long explanation. And I imagine that Barry gave him more than one hour.

I saw your message two weeks ago; today I decided to post a solution because I did not realize that Barry´s was a complete answer. At least, it seemed so, although only the topic starter could say.

Well, it took me 2 hours (I do not type with all 10 fingers) in building the DB, with 16 records and all the fields of the list plus those summary ones. After, I spent some time thinking until I got the principal formula:

PrizeCertificate=10*IF(SUM('Amount Raised')<=49.99,"",1+INT((SUM('Amount Raised')-50)/100))

Before I added some fields more, to discount the Certificates from the total, I copy Barry´s formula to test it and it became clear that his was a lot simpler one:

PrizeCertificate=10*INT((50+SUM('Amount Raised'))/100).

I would offer to send the DB and help with more explanations, but the poster did not return in more than one month.

I can guess what you, Barry and others can say if you read this; even if the topic starter did not return, there is always a colateral effect that we can get answering posts here: others can learn and this is the purpose of the forum.

Regards,

Lucas (the guy from Rio Grande do Sul)

Re: Database

PostPosted: Mon Apr 24, 2006 11:34 pm
by Barry
Jorge Lucas wrote:
haumann wrote:
Barry wrote: ... snip ::: 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. ::: snip:::


Barry,

The same thought crossed my mind. I just thought this might be an awfully ambitious DB introduction. None-the-less, you've given the project a good start.

Now, where is Lucas?

[email protected]


John,

I was in vacation and with no Internet access at home for almost a month.

Besides that, there are two reasons for my absence in the last times:
1) when I see some topic regarding SS and DB, almost always someone answered it already and...
2) I am a little disappointed with some posts I wrote and got no return messages saying that they worked or not, specially when the asker was a guest or a first-time poster.

My purposes in trying to give a solution for a problem that was not mine at first are: help those who needs help and improve my ability in solving problems.

But this requires time and is so frustrated when you feel you spent that time for a stranger that did not cares about it.

I got people that gave me real important answers, in the forums, and I always gave them my thank you or I asked for complementary explanations.

I can guess what you, Barry and others can say if you read this; even if the topic starter did not return, there is always a colateral effect that we can get answering posts here: others can learn and this is the purpose of the forum.


Hi Lucas,

Good points. In this case, Steve (the original poster) did contact me directly with a thank you, and a supplementary question or two. I agree that in the general case, a 'Thank you, that solved my problem' message wold be a useful one, both in terms of acknowledging the solution and in terms of telling others that the problem had been solved.

Regards,
Barry

Re: Database

PostPosted: Tue Apr 25, 2006 6:48 am
by sschnyd
Lucas,

I am sorry you did not hear my whole correspondence with Barry. I am very thankful for the help he and others have given. The event went very well and the database was just what I needed. I know how much time can be put into databases and spreadsheets. I have put together a spreadsheet for our school for the Presidential fitness. So let me be sure to let all of you people who post with your help that I for one am very greatful.

Thanks again,
Steve

Re: Database

PostPosted: Tue Jul 05, 2011 1:45 am
by AndrewNZ
Hi,

I can assure you there is value in posting answers and alternative answers on the forum, even if it appears as though the OP has not read the advice or posted an acknowledgement of the same.

I'm grappling with functions in an AW database and this is the closest thing I've found to a helpful answer on the net.

I'll post my question in a new thread for simplicity's sake, but thanks again for the advice provided in this thread.

Andrew