School Awards Program

[Jerry Coleman]

I got volunteered to create a database for our school rewards program this year. I decided that Access would be the best choice to accomplish this task, but I'm not sure exactly how to go about some of the programming. I am going to have a table that has the student's names, the day they receive a reward point, a field for number of points added, and a field for number of points to subtract. Then I would like to create a form that would make entering this data easy for the teachers. My problem is, how do I get a balance for each child?

Thanks for any help you could give me...

Jerry

 

[pbaldy]

Welcome to the site. Generally, a totals query would be a common way:

SELECT StudentField, Sum(PointsField) AS TotalPoints
FROM TableName
GROUP BY StudentField

 

[Jerry Coleman]

Hi Paul,

Each student would have a separate total of their own. In the student name field, I will have 433 different students entered several times in random order. When I total up the number of rewards for one student, how could I just add their totals and place the balance in their total field? Also, I don't understand what you mean by "Select","From" and "Group"?

Thanks for your help...

Jerry

 

[MSAccessRookie]

Hi Paul,

Each student would have a separate total of their own. In the student name field, I will have 433 different students entered several times in random order. When I total up the number of rewards for one student, how could I just add their totals and place the balance in their total field? Also, I don't understand what you mean by "Select","From" and "Group"?

Thanks for your help...

Jerry

"Select","From" and "Group" are SQL Commands that allow you to obtain data from the database.

In the example, pbaldy is selecting the StudentField and adding up the points that are received.

The SUM command will add up the points.
The GROUP BY Command will ensure that the sums are done for each group set (in this case the StudentField), so you will get one row for each StudentField entry

You mentioned another field for "points to subtract" as well. I am not positive what that would be for, but I think it means that the query needs a minor modification:

SELECT StudentField, Sum(AddPointsField) - Sum(SubPointsField) AS TotalPoints
FROM TableName
GROUP BY StudentField

 

[pbaldy]

What I posted is a type of query. If your table contained:

Paul 4
Joe 2
Paul 1
Jerry 7
Paul 2

That query would return

Paul 7
Joe 2
Jerry 7

Is that what you're after? Most of us would recommend against storing the grand total by the way. Just calculate it on the fly.

 

[Jerry Coleman]

Paul,

This is exactly what I'm trying to do... I'm new as using Access, but I'm eager to learn. Where would I apply the code your showed me?

Jerry

 

[pbaldy]

Create a query that returns those 2 fields. Click on the "Totals" icon in the toolbar/ribbon (looks like a big E). That will add a Totals line in the query and both fields will default to "Group By". Change the points field to Sum and run the query.

 

[Jerry Coleman]

This is working great. I've got another question?

Say I have four students,

1. John Doe
2. John Adams
3. Jane Doe
4. Sue Lester

When I select the first name "John", how could I make the last name field just show "Doe" and "Adams" to choose from?

Thanks for all the help...

Jerry

 

[pbaldy]

Are we talking about combo boxes here? If so, the concept is called cascading combo boxes and I've got a little sample here:

http://www.baldyweb.com/CascadingCombo.htm