Storing history data

[teser3]

I have an Access 2000 database that is populated from a Web Front end
application. The database usually stores about 100 records and has 1 or
2 concurrent users. I have a field called Status that stores about 10
to 20 sentences of Status information that is entered once a week.
Each time someone updates a Status field it copies the old status to
History1 field and History1 is copied to History2 field and History2
field is copied to History3 field.

Everything works well but now I need to know how I would store the old
data that is eliminated in the History3 field. I could put it in one
big field called Archive but I think Access limit on a memo field is
65,000 characters?

Please advise how I should store this information because status is
updated once a week and I will sometimes need to store up to 52 weeks
of data.


My table fields:
project_id
status
history1
history2
history3

 

[Guest]

Your database is set up incorrectly and the fancy moving of data between
fields is unneeded.

You need one table with the basic information and another table with the
Status information. There should be a link between the main table and the
Status table. Each record in the Status table should be for only on status on
a certain date. That way you can store past status for 52 or 520 weeks if you
wish. Just make sure that the Status record has a date field in it with a
Default value of =Date() so that you can tell when a status was entered.

 

[teser3]

If I am understandng this correctly I can have a Status table that will
always create a new record for each archive entry that is coming from
my project.

So my info for example if I have 2 projects with history will look like
this if I have archive for 2 weeks:
Project table
Project Id Status
1 Long sentence here
2 data status here


Status Table
StatusID ProjectID Archive StatusDate
1 1 Next alot of sentences here 2/1/06
2 2 alot of words here 2/1/06
3 1 many many words here 2/8/06
4 2 alot of junk here... 2/8/06


My insert statement using the front end variables:
INSERT INTO status(projectid,archive,statusDate)
values(projectid,'#status#',date(Now))
where projectid = '#form.projectid#';
INSERT INTO project(status)
values('#form.status#')
where projectid = '#form.projectid#';


Please advise if I am in the right direction or anything I need to do
to make this work?




My table fields: