history, traceability of a event

[avacess]

guys,

i am trying to build a table to store all the history events for a task: like, when was carried out (date/time format), who carried it out, using how many resources, and so on. all these info are different types of formats: from date/time, number and text.

i want to create a ContentTable to hold all these tasks (could go up to hundreds), and then a TaskTable related to the ContentTable for each individual task. in the TaskTable i want to record all the history of that single task. these tasks could be carried out on a daily basis, or weeks (if makes any difference).i am not sure about the functionality of this, if the combination of a big table and then hundreds of other tables could work, and how will access react when working with this data.

but my biggest problem is that i am not sure where to start from. in the end i need to be able to report all these info as a history report for each individual task.

i would appreciate any input of yours.
regards,
george

 

[VilaRestal]

All of it sounds perfectly regular except when you say:

i am not sure about the functionality of this, if the combination of a big table and then hundreds of other tables could work

Firstly, hundreds of records is not big table. Hundreds of thousands is

But more worryingly "hundreds of other tables"????

Are you thinking of creating a Task table for each record in the Content table? If so, for god's sake don't. Just one Task table for all Content with a field showing which content the Task refers to. You can then select those records from that for a particular Content.

The names are a bit misleading then. What you call the ContentTable I would call the TaskTable (lists all tasks) and what you call the TaskTable(s) I would call the TaskHistoryTable (single table for all tasks)

That TaskHistoryTable may end up with tens of thousands of records in it. Access will handle one table with tens of thousands of records better than a hundred tables with hundreds of records each. (and so will you in terms of designing queries and forms based on that one table or hundreds of tables - the latter would be a massive headache)

 

[avacess]

VilaRestal,
Thank you for your prompt reply.
I will take in consideration your opinion. I was looking on the forums for something related to my issue, but unfortunately I couldn't find at anything relevant to such a 'construction'.
do you propose anything where to start from?
george

 

[spikepl]

See if you can find a suitable template you could shave down to your needs: http://office.microsoft.com/en-us/templates/CT010152732.aspx?av=ZAC

 

[VilaRestal]

Presumably you would have two tables with fields something like this:

Table tblTasks
with fields:
ID (primary key, autonumber),
other task fields (description perhaps)

Table tblTaskHistories
with fields:
ID (primary key, autonumber)
TaskID (number, enforced relationship to tblTasks.ID)
DateHappened (date)
ActionedBy (text or number related to table of people who do tasks perhaps)
other task history fields

(I've named them as I would, name them as you please)

If tblTaskHistories might ever contain tens of thousands of records or more then making its fields efficient is important (using numbers that relate to text in lookup tables rather than text for example).

It's a simple structure and one that will easily lend itself to designing queries and forms based on these tables

 

[avacess]

vila, in the end i tried to follow your advice. i created two tables. the tblTasks and the tblTasksHistory. the idea behind i would like to have is something as follows:
1. same tasks from tblTasks are in the tblTasksHistory. in the tblTasks are only the last updates of each task. in the tblTasksHistory are all the events for all the tasks, meaning:
2. everytime i update a task, it will be recorded in tblTasks and only this current update, and in the tblTasksHistory will be recorded as a new enty, and all the previous updates are kept. that means i will have duplicate entries for tasks, but different details for carried-out date, and by whom.

if you have any idea please let me know. i am searching continuously for something similar and i have no clue until now.
10x

 

[avacess]

i think i am getting closer. append query
as Einstein was saying, i need to put the correct questions.