Proper table design for monthly tracking of activities

[Tim]

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.

 

[KARL DEWEY]

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.

 

[Steve]

Hello Tim,

Consider these tables:
TblPerson
PersonID
FirstName
LastName
etc

TblCheckItem
CheckItemID
CheckItem

TblNumericItem
NumericItemID
NumericItem

TblCheckItemCompleted
CheckItemCompletedID
PersonID
CheckItemID
DateCompleted

TblNumericItemCompleted
NumericItemCompletedID
PersonID
NumericItemID
NumericItemScore
DateCompleted

For reporting, consider a report and two subreports. Base the main report on
TlPerson. Base the first subreport on a query that joins TblCheckItem to
TblCheckItemCompleted. Base the second subreport on a query that joins
TblNumericItem and TblNumericItemCompleted. Set the LinkMaster and LinkChild
properties of each subform control to PersonID.

Steve
(e-mail address removed)

 

[BruceM via AccessMonster.com]

I may as well weigh in too. I was working on this, then others answered, but
this is a different approach than either of the responses I have seen so far.

You are correct that you need another table, as adding fields is not going to
work. Actually, I think you need several tables. One is the current People
table (tblPeople). Another will be the list of seven items (tblItems).
Presumably this list could change over time, so a table will give you
flexibility. Even if the seven items never change, a table is probably the
best way. You will also need a PersonMonth table (tblPersonMonth) with a
record each month for each person. It may have only its own PK field, a
Date/Time field for the month and year, and maybe a Comments field. Then
there will need to be a junction table (tblMonthList) between tblItems and
tblPersonMonth.

tblPeople
PeopleID (PK)
FirstName, etc.

tblItems
ItemID (PK)
Item

tblPersonMonth
PersonMonthID (PK)
ListDate
Comments

tblMonthList (junction table)
MonthListID (PK)
PersonMonthID
ItemID
Result (Number)

PK fields are all either Number (Long Integer) or Autonumber (which is in
effect a Long Integer field). There is a relationship between PersonMonthID
(tblPersonMonth) and ItemID (tblItem) and the like-named fields in
tblMonthList. They do not need to have the same name, but I have used the
same names for this description. It's up to you.

The general idea is that you would create a record in tblMonthList each month
for each person, then populate tblMonthList with the items in tblItem. This
assumes all of the items in tblItems apply to each person each month. In
terms of the interface, you could have a main form based on tblPeople, with a
subform based on tblPersonMonth, which would in turn have a (continuous)
subform (sfrmMonthList) based on tblMonthList.

One little complication is that some fields are Yes/No, and others are Number.
However, Yes/No can be stored as Number, so one Result field could be used.
Similarly, Result could be a Text field if text values are needed. Numbers
or Yes/No could also be stored as text, in case the numerical value also
contains text. Frankly, I wonder whether a single Result field is the best
choice, but I think it is a workable solution.

Anyhow, after creating a PersonMonth record, you could use code to populate
tblMonthList, maybe something like this in a command button Click event:

Dim strSQL as String
Dim lngPerMon as Long

lngPerMon = Me.PersonMonthID

strSQL = "INSERT INTO tblMonthList " & _
"(PersonMonthID, ItemID, Result) " & vbCrLf & _
"SELECT " & lngPerMon & " AS PersonMonthID, ItemID, Null " & _
vbCrLf & "FROM tblItem;"
DBEngine(0)(0).Execute strSQL, dbFailOnError

As an alternative to the code you could use tblItem as the Row Source for a
combo box on the continuous subform sfrmMonthList, bound to ItemID. Rather
than use the code to enter all items at once, you would enter each of the
seven items individually, probably recording the result as you go.