A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Proper table design for monthly tracking of activities



 
 
Thread Tools Display Modes
  #1  
Old December 29th, 2009, 02:11 PM posted to microsoft.public.access.tablesdbdesign
Tim
external usenet poster
 
Posts: 780
Default Proper table design for monthly tracking of activities

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.
  #2  
Old December 29th, 2009, 04:40 PM posted to microsoft.public.access.tablesdbdesign
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Proper table design for monthly tracking of activities

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.

  #3  
Old December 29th, 2009, 04:52 PM posted to microsoft.public.access.tablesdbdesign
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Proper table design for monthly tracking of activities

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





"Tim" wrote in message
...
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.



  #4  
Old December 29th, 2009, 07:16 PM posted to microsoft.public.access.tablesdbdesign
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Proper table design for monthly tracking of activities

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.

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.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200912/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.