Storing data

[evergreean]

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.
Basically never lose any history data. 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.


Table fields:

Project_id
Status
History1
History2
History3

 

[PHV]

Why not simply this ?
Project_id
DateField
Status

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[evergreean]

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 for a short version for example only if I have 2 projects with history information I would have a Project table:

Project Id      Status
1               Long sentence here
2               data status here

Then have a Status table (with ProjectID as foreign key) showing two weeks here:

StatusID  ProjectID  Archive                StatusDate
1         1          alot of words here     2/1/06
2         2          alot of wordshere      2/1/06
3         1          many many words here   2/8/06
4         2          alot here...           2/8/06

And my insert statement using form entry 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 right direction or anything I am doing wrong?
I plan on having about 200 records that will be updated with status once a week and not alot of concurrent usage.