Use a people table with ID (Primary key) and Active field. Active or
InActive field can be a Yes/No or a DateTime field indicating when departed.

Second table TaskComp has ID (foreign key), TaskMonth - DateTime, Task1,
Task2, Task3 (number fields), Task4, Task5, Task6, and Task7 (Yes/No or
DateTime to indicate when completed).

Use a form/subform to display/enter data into people/task records.

Before the end of the month run an append query to add records to TaskComp
table for every active person with TaskMonth date of first of next month.
Update records as task are accomplished using the form/suform from query with
current month as criteria.

--
Build a little, test a little.


"Tim" wrote:

Access Knowledge level - medium low (can create tables, link them, basic
queries, basic forms and reports).

Using Access 2003

I have 48 people who have specific activities they are supposed to do each
month, and I have to report on the completion of these. I built a table
defining the 48 people. There are 7 items to be tracked each month and
reported on. 4 of these items are simply a checkmark if it is done, the other
3 monthly items are numerical results. For the report, I need to list the 48
people, and show the results of the recent 6 months. It is possibile that a
person may complete any one of the 7 items items, multiple of them, all of
them, or none of them.

Currently I have a bad table design because I cannot figure out how to store
the monthly data, and report on it. Currently I have a single table, with
the 7 items included, and each month I add a 7 new fields representing the
task and the month.

I recognize I need to establish this as a second table with a primary key of
year, month, and person id, and the seven fields. I haven't because I can't
figure out how to report progress, due to potentially missing records
(noncompleted tasks).

The best idea I have so far is to default a N for not completed in each text
field for completed tasks (not using Y/N field), and a 0 for numerical score.
then in query use a prompt for records greater than a date for user to
input, and input to report layout. Previously if a task wasn't completed, it
was blank and therfore easy to identify what had not been done. I fear that
having default values for uncompleted tasks will make the report massively
busy and hard to read.

I really could use some experienced help/guidance. If you know of a site
that may have similar questions or a solution, a link to that site would be
great.