Many tables or large field size (1 Viewer)

[speedball]

Hello

Any advice on the best approach to take regarding the following issue would be appreciated.

I have a table which stores information about the progress of various tasks. There is a record for each task, and a field for each time the progress is updated (Prog1, Prog2, ..., Prog50).

Each time the progress of a task is updated I want to store the following 5 pieces of information:

- Progress stage
- Date updated
- Date effective
- Person who updated
- Comment

At the moment I have one table that stores 3 pieces of information for each task. When I want to view the progress of a certain task I have a separate table and split out the 3 pieces of information using VBA code.

However, I now want to store 5 pieces of information (as above) and am wondering whether I'm taking the best approach.

Should I instead have a separate table for each piece of information? I guess that would make it more tricky to ensure that the pieces of information for each stage of progress for each task were kept together, but it would avoid having to split out the information each time I want to view it.

Or should I be taking a different approach entirely?

My main concern is with the speed of the database. Any advice would be welcomed.

Thanks in advance
Speedball

 

[plog]

You need at least 2 tables---Tasks and Progresses.

Tasks would store information about the individual tasks and a unique ID number to identify the task:

TaskID (autonumber)
TaskName (text)
TaskNote (text or memo)

Progresses would store updates to the tasks, all that data you listed above and 2 unique ID numbers:

IDTask (number which links to Tasks.TaskID)
ProgressID (autonumber)
ProgressStage (number which links to Stages table or text)
ProgressUpdate (date)
ProgressEffective (date)
Progressor (number which links to People table)
ProgressComment (text/memo)

Then to extract your data or use it in a report you would link those tables in a query and obtain the data you need.

 

[speedball]

Thanks plog. Sounds like a good approach.