T
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.
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.