Database design - help getting started

[Timothy.Rybak]

I need help designing a database that keeps up with a point system we
have here at work. I'll tell you what I know.

First, I have a list of all the employee names for a table.

Second, for various reasons that I don't want to go in to, people can
earn points, in half point increments, toward a reward.

Now for the tricky part. The points are calculated on a 12 month
rolling sum. For every 3 months that an employee goes without getting
any points, a point is dropped from their total. Also, at the 13th
month, any points earned during the first month are dropped from their
total.

Given these rules, here is what I need out of this database. Every
day, the supervisors will need to open the database, and use a form to
pick the user's name, enter a date, points earned, and any comments.
Then, they should run one of three reports - 1) show me all of the
names and how many points they currently have, and 2) show me the
people that have gone 3 months without earning any points, and 3) show
me the people that have gone all 12 months without earning any points.

So, I know I will need a table of employee names, and a table to hold
the data that is entered by the supervisors. It's the rest of it... I
can't figure out how to get started.

Any help is appreciated. Please write if you need more information
about any part of this.

Thanks!
Tim

[Phil Stanton]

2 Tables

1 Employees - Fixed information - EmployeeID as the key (Autonumber) then
LastName, FirstName etc etc etc

2) Points - PointID as the key (Autonumber) EmployeeID Long Number, Points,
Date, Comment etc

Enforce a relationship between the 2 tables

Then your information will be obtained by selecting the appropriate date
range. Generally you will not delete points at the end of 12 months, but
your rolling period is just found by changing the dates so last month you
want SumOfPoints from Dec 1st 2005 to Nov 30th 2006, This month you want
SumOfPoints from Jan 1st 2006 to Dec 31st 2006.

At the end of each month, say Last December you run the report based on
SumOfPoints from Oct 1st 2006 to Dec 31st 2006 where SumOfPoints = 0 and
either manually or using VBA code add a -1 point to the points table.

Rough outline of where to start

HTH Phil

<Ti***********@ gmail.comwrote in message
news:11******** *************@i 39g2000hsf.goog legroups.com...

>I need help designing a database that keeps up with a point system we
have here at work. I'll tell you what I know.

First, I have a list of all the employee names for a table.

Second, for various reasons that I don't want to go in to, people can
earn points, in half point increments, toward a reward.

Now for the tricky part. The points are calculated on a 12 month
rolling sum. For every 3 months that an employee goes without getting
any points, a point is dropped from their total. Also, at the 13th
month, any points earned during the first month are dropped from their
total.

Given these rules, here is what I need out of this database. Every
day, the supervisors will need to open the database, and use a form to
pick the user's name, enter a date, points earned, and any comments.
Then, they should run one of three reports - 1) show me all of the
names and how many points they currently have, and 2) show me the
people that have gone 3 months without earning any points, and 3) show
me the people that have gone all 12 months without earning any points.

So, I know I will need a table of employee names, and a table to hold
the data that is entered by the supervisors. It's the rest of it... I
can't figure out how to get started.

Any help is appreciated. Please write if you need more information
about any part of this.

Thanks!
Tim