Calculation Equation

[mtk]

I need help writing a calculation.

I have a table with these 4 fields in it.

Field1-Empty_Boxes

Field2-Empty_Box_Running_Total

Field3-Empty_Box_Points

Field4-Date

Members receive 1 pt for every empty box brought in up to a max total of 10 boxes.

If a member brings in less than 10 boxes they get 1 point for each box.

(Note: They can bring in 1 box each day until they amass a total of 10)

If a member brings in more than 10 boxes they get 0 points for those boxes.

I still wish to count the total number of boxes brought in although they get no points for it.

1. I can't even get Field2 to work properly. Here's what I have: Empty_Box_Running_Total=Sum(Empty_Boxes) It ought to be an easy Calculation-sum of Empty boxes but it does not work. What am I doing wrong?

2. Once I get the Running total for Empty boxes, do I use an IF statement, CASE, Get--What does the statement look like?

Seems to me it would look something like:

Case (

Empty_Box_Running_Total ≤ 10;Empty_Boxes;

Empty_Box_Running_Total >10;0;10-Empty_Boxes)

Thanks in advance for helping me out with this. I'm a newbie, can you tell?

[comment]

They can bring in 1 box each day until they amass a total of 10

It looks like you should have two tables, one for members, one for the boxes they bring in - related by MemberID. Then you can sum the boxes of each member by doing a calculation in the Members table =

Sum ( Boxes::Amount )

and the points = 

Min ( 10 ; cSumBoxes )

[mtk]

It looks like you should have two tables, one for members, one for the boxes they bring in - related by MemberID. Then you can sum the boxes of each member by doing a calculation in the Members table =

Sum ( Boxes::Amount )

and the points = 

Min ( 10 ; cSumBoxes )

I think I need more help.

I am trying to collect 3 distinct types of info on each member. (hours worked, boxes brought in, jobs done)

I have a Member table and an Hours table and a Boxes table to collect hour and box info on each Member.

The Hours and Boxes table are linked to the Members table via a Data Entry table. PK and FK in data entry.

The Hours and Boxes table are linked directly to the DataEntry table via keys.

Is this part correct?

I then have a Job Activity linked to the Members table through a Job Registration table. PK and FK in registration table.

Is this correct?

This calculation Min ( 10 ; cSumBoxes ) is giving everyone 10 points even if they did not bring in any boxes.

[comment]

The Hours and Boxes table are linked to the Members table via a Data Entry table. PK and FK in data entry.

I don't get this part. What is "a Data Entry table" and why is it necessary? I think both Hours and Boxes table should be related directly to the Members table, matching on MemberID (and nothing else).

I then have a Job Activity linked to the Members table through a Job Registration table. PK and FK in registration table.

Is this correct?

I don't know. You meed to explain in more detail what do these tables represent in real life.

[mtk]

I don't get this part. What is "a Data Entry table" and why is it necessary? I think both Hours and Boxes table should be related directly to the Members table, matching on MemberID (and nothing else).

I don't know. You meed to explain in more detail what do these tables represent in real life.

I'm trying to create a db used in a kiosk setting to collect points on a daily basis on each member for hours worked by type (general and category), boxes collected from different sources (home,dropbox and empty), other jobs (Board member, BookSale Committee Chair, Category Chair). Each one of those services generates points for the member and is input on a daily basis. I'm trying to collect points for each member and total work done and boxes collected for the organization.

My members are typically 60-80 years old and not tech savvy. I want the screen they see after they login (the data entry screen-my join table) to be as clean and simple as possible so as not to confuse them.

Because each member could input more than once daily and have MANY hours, boxes, jobs I thought I needed a join table to make sense of the relationship. I guess this is one of my biggest confusions.

All three service activities (hours, boxes, jobs) require different calculations and are separate entities unto themselves. It doesn't make sense to me that a person would arrive on 6/1 and input 4 hrs of general sorting then turn around and have to input 6/1 a home pick up of 11 boxes. Those should both be entered once on 6/1. I think that's why I have the join table.

I want them to start on a login screen (I've created a dropdown where they select their name)

Then they would be sent to a data entry screen (daily points collection) via a script button.

Here they would enter date, gen hrs, category hrs, home boxes, dropboxes, empty boxes.

Points would be calculated based on their input and passed to a summary screen.

It would show a summary of the member's information with the past info input sorted by date.

They then would be taken back to the login screen via a button for the next volunteer.

Your assistance is very much appreciated as I've been working on this for months and seem to get nowhere because I keep changing my approach.

It seems there are many ways to skin a cat. My friend wants me to do it with ALL global fields and not use relationships. Yikes. That sent me for a loop for a while.

Thanks again!

[comment]

Because each member could input more than once daily and have MANY hours, boxes, jobs I thought I needed a join table to make sense of the relationship. I guess this is one of my biggest confusions.

Each member could have MANY hours, boxes, jobs - but each hour, box or job assignment belong to ONE member only. This is a straight-forward one-to-many relationship and no join table is required. Don't make the mistake of designing your data model around the user interface.

It doesn't make sense to me that a person would arrive on 6/1 and input 4 hrs of general sorting then turn around and have to input 6/1 a home pick up of 11 boxes.

I don't see why they need to input the date at all (assuming they are reporting on the current date). In any case, they could input related data from their record in the Members table. It looks like some scripted assistance, perhaps even a few global fields, could be helpful here - it's hard to judge without seeing what the data entry screen should like.