Keeping previous score...how would you go about it ?

Hey guys,

For the past few weeks, I've been working on a project, that basically attributes points to a patient, based on two factors; the answers he gives on a form, and the multiple tests results.

Now I have the tests results covered. Basically, I have a table on a different tab, where all the tests possible are listed vertically, and the secretary (or whoever job this is) can then enter the results. There's 100 columns right now, so you can enter 100 different results, for the same test, chronologically. The summary page then looks through that table to find the last result available, and the one before that, gives a variation between the two, applies the current score, and calculate the previous one, yadi, yadi, yada. So that works.

My problem is with the answers given on the form. Unlike the tests results, the summary page can't find any "previous" data, as there's only ONE form per patient, that you can update, by changing the "X" in the appropriate cases. It was initially designed that way to save on entry time, and to allow the patient to mention only changes, rather than having to complete the whole form one more time. The form covers lifestyle habits and medical history, so a lot of infos aren't really subject to change (if a relative died from cancer at the age of 67, chances are he'll still be dead at the next evaluation).

However, other infos could change...things like smoker/non-smoker, etc.

Do you see a way that would allow me to keep only one form per patient, and still get the previous result ? Right now, the only solution I have in mind is to create two forms; one "ACTUAL" and one "PREVIOUS", have the staff copy the ACTUAL over the PREVIOUS manually each time, and apply that system to the formulas on my summary page, so that the column "ACTUAL" will look at the answers given on the "ACTUAL" form, and the column "PREVIOUS" will take the answers from the "PREVIOUS" form.

But it seems a bit tenuous and unsafe to work that way.

So if you have some ideas or advice, I'm all ears.

Thanks.