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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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. |
#4
|
|||
|
|||
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 | |
|
|