Make a database having a history access table

[Polongo]

I'am making a medical databases.
The database is needing another table in MS access to set it to history of all patients.
Once i edit the existing record its history will be recorded and should be save in the history table in MS access and the the new that is being replace to the old record will be recorded in the Main MS access.
how can i made it?

Plz help me.. It will helps me a lots.
Thankz.

[Shaggy Hiker]

So you are talking about two, pretty nearly identical, tables. When a new record is added, the existing record is moved to the other table, then the new record is added. Is that about right?

I am dealing with a similar situation...sort of. Whenever changes are made to a record in one field, the previous state of the record needs to be saved and a new record created. In my case, it is also important to note when the change took place. Therefore, I have a CreationDate and a TerminationDate (actually, I don't, but that's close enough for this description) in the single table. When a record is written, the new record has the current date as the CreationDate and a Null for the TerminationDate. The previous record, if there is one, had a Null for the TerminationDate, and that is updated to the current date. Therefore, the current records can be obtained by querying for where TerminationDate Is Null, while any past information can be obtained by querying for the opposite, and they can be ordered sequentially by the CreationDate.

[Polongo]

So you are talking about two, pretty nearly identical, tables. When a new record is added, the existing record is moved to the other table, then the new record is added. Is that about right?

Yeah. were actually the same.. the old record will be moved into another table(History) and the new record will be replace to the old record.

How can we do it?

[Shaggy Hiker]

I would suggest not even trying to move the records. Heck, I'm not even sure that you really CAN move a record. Instead, create a new record in the history table using the values from the current record, then delete the current record from the current table. Alternatively, if you are using a dataset with datatables for both the current and historic tables, and the tables really are identical in structure, you might be able to detach a record from one and append it to the other. Adding it to the history table would be pretty straightforward once it has been detached from the first table, but I don't know that step.

[Evil_Giraffe]

If you used a different database than Access then you can do this very simply with Triggers. Have an update trigger that copies the old values to the history table with a timestamp of the update. SQL Server is available in a free Express version. Access can connect to and use this as its storage, apparently, so it's still (kinda) good if you're required to use Access.

[Hack]

Moved To Database Development

[baadil]

You can leave them in the same table. Simply have field EndTime. When a new record is created, record the current date/time in the existing record and then create a new record with endTime as Null.