New record with most of previous records values 1

[UHNSTrust]

I have a database that stores assessments. It is basically a field to link to a table called tblPatient, a date field and 24 question fields. When a new assessment is done it will only usually be 1 to 2 answers to questions that will have changed. The rest are the same as the last assessment. What I have been asked to do is create a new assessment but copy the last assessments answers to the questions and put a new date on the record. Then the user changes the answers they want.

So the table has the following fields;
AssessID
PatientID
AssessDate
Q1
Q2
...
Q24

In a stored procedure - How do I copy the last assessment for a particular PatientID but put the date as a passed in parameter? I then need to return the ID field of the newly created record so that I can show it within my Access front end.

Hope this makes sense. Any help is appreciated. Thanks in advance.

Jonathan

 

[philhege]

Jonathan.

You need a database makeover STAT.


The questions should be in a separate table. The answers should be in a separate table.

Patients
Field datatype
(whatever you need here)


Questions
Field datatype
Q_ID int
Q_text text (or varchar(max), varchar(2048), something)

Answers
Field datatype
A_ID int
A_date datetime
Patient_ID int
Q_ID int (FK to the Questions table)
A_text text

And the correct relationships among the tables. Now you'll have an ongoing journal of Q and A, and you won't have to re-design tables if the number of questions goes beyond, say, 24.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[SQLSister]

Here's one method

declare @newidentity int
insert into table1 (field1, field2, field3)
select top 1 field1, field2, getdate() from table1 where field1 = 123 order by table1_id desc
set @newidentity = scope_identity()
select @newidenitity

"NOTHING is more important in a database than integrity." ESquared

 

[UHNSTrust]

Thanks for the responses.

Phil, I had set up the database with seperate tables as you had suggested. However I could not get the user interface to work properly (Access front end). Using your method would have 24 records which I would have to display on a continuous form. However the spacing on the continuous form is a problem as some of the questions take up more space than others (2-3 lines of typing). I have only got a certain amount of screen space due to user specification. Access does not do Grow/Shrink in form view (only print/preview). I appreciate what you are saying and know you are right. If you have any suggestions as to how I can show this better from a user interface view then it can be changed back quite easily.

SQLSister, thanks. Will try your code and let you know how I get on.

Jonathan

 

[UHNSTrust]

SQLSister,

Just to let you know it worked great. A star for you.

Jonathan

 

[gmmastros]

Just to be clear... You modified the structure of your database to accommodate the user interface?

That's just wrong.

If you store the information that way the Phil has suggested, you can still return the data in XNF (Excel Normal Form) by being a little crafty with your query.

It seems like you are in the early stages of this project where you still have time to do the right thing. If you would like to normalize your database, and would like assistance with the query, just say so.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[UHNSTrust]

George,

I have not come across the term XNF. How would this help me present the data on a form (i.e. 24 records). The data still has to be editable through the form.

I know the way Phil suggested is right and that is how I developed the database to start with. The problem is that I need a continuous form that grows/shrinks to accommodate the records in a small screen area. I do not know a way to show this information in Access.

I have to have a working example by Friday so am being forced to develop in a way that will work even if it is not ideal. This database is only going to be used for approximately 3-6 months until the developmnet boys have time to set up a web front end.

If you can help with how to present the data to a user in an editable form in Access I will be more than happy to change the database structure. I am sure people have come across this before and overcome it.

Thanks in advance for any help.

Jonathan