Help with Duplicating and then updating records
I had some help last week here (THANK YOU!!) and am stuck taking things to the next step, so would be really grateful for any advice.
I am (very slowly) creating a database for my husband, a Doctor. It contains tables relating to the patient, the visit, the drugs etc
Each time a patient visits a new visit is created where he inputs their results.
I'm having real trouble though with the drugs. What I want to do is for him not to have to type in their current drugs at each visit. I want the layout to show the drugs he prescribed last time BUT he must not over write the records from the last time.
I have (amongst other things) a table patient, table visit and table drug
I have a table occurance that calculates their last visit id and the drugs that are assiociated with this last visit so I have the base data
But now I'm stuck on how to take those records (the number will vary for different patients) and effectively duplicate them into new drug records for the new visit without loosing them as records from the last visit.
I really would be grateful if anyone can point me in the right direction I've read and reread the big book and am now more confused.