General Advice Needed Please

[Paul Cooke]

Hi

I am re-designing an old DB that was used years ago. It is used to store records on patients who have recieved treatments for injuries, vaccinations ect.

I have a table called TreatmentTable, with fields including :

FirstNameOfPatient, SurnameOfPatient & PatientDOB (date of birth)

I am currently designing the form so users can enter the records of the treatments and wondered if there is anything I should bear in mind for the future use of the DB such as searching / calling up previous records,

The way it is at the moment on the form a user would simply enter the first name, then surame, then dob and all the fields ect.. If the same patient came back again a couple of months later then currently the users have to go through the whole process again.

I think bascially what i really need is for someones treatment records to come up so a new treatment can bee added to it..

sorry if this is confusing if you need clarifaction on any point please let me know

Many thanks

Paul

 

[DCrake]

You could add an extra field in your patients table such as NHS Number, this is unique to each patient and every patient will have one. You can then use a combo box of all the NHS Numbers, when you select one it filters the records based on the select number.

You also need a treatment table that has the same NHS Number as a foreign key to link the 1:M table relationships.

 

[pbaldy]

The typical normalized design would have two relevant tables, Patients and Treatments. The Patients table would have the fields you mentioned in it, along with a patient ID. The Treatments table would contain info about the treatment, and would only have the patient ID in it to identify the patient (for relational design reasons, not security reasons).

A common setup during the entry of treatments would use a combo box to look up patients from the patient table. You can use the not in list event of the combo to add new patients to the patient table.

 

[Paul Cooke]

Many thanks guys for the quick responses!

Re the NHS Number - I am currently racking my brain as to what to use to identify patients as most people do not know their NHS number or even their NI number I was thinking of using a postcode which is something most people can remember !

Paul - If I am understanding you correctly I am asumming you mean the following...

Create a Patient Record Table - Set primary key a Postcode for example as above

Create a Treament Record Table - I am not sure what you mean by "and would only have the patient ID in it to identify the patient "

When the user goes to enter a new treatment a "Patient Details" form opens and they either select a current patient or enter a new one using the not in list event
then the treatment forms opens and they enter the details of the treatment?

Thanks again

 

[pbaldy]

I can't imagine that postcode would be a good primary key, as it isn't unique to each person (is it?). Failing the NHS number David mentioned, I'd probably use an autonumber.

The treatment table would not contain the patient's name, address, dob, etc. Only the ID from the patient table.

Not really. The user would be working on a form bound to the treatment table. They would choose the patient using the combo, but if they entered someone that didn't exist in the patient table, the not in list event would trigger a form to open to allow you to enter them into it, then return to the treatment form. Searching here for "notinlist" should turn up the relevant code and examples.

 

[Rabbie]

Re the NHS Number - I am currently racking my brain as to what to use to identify patients as most people do not know their NHS number or even their NI number I was thinking of using a postcode which is something most people can remember !

Don't want to be a wet blanket but it is possible that more than 1 patient could have the same post code even if they don't live in the same house.

 

[DCrake]

When you fill in the details of patient treatments, I would asume that the source of the data would be a form of some sort completed by a healthcare professional that is ultimately attached to their patient record. The treatment would be most likely done in a hospital or some other treatment place. The patient would have attended in response to an appointment. That appointment would normally have been sent to the patient via a letter. This letter would most likely contain the patient's NHS Number. So there you have serveral instances where you could source the NHS Number.

Once a person has been given a NHS Number it stops with them for life. Unlike a postcode that can change from one day to another. Then again what happens if they live on a canal boat?

 

[Paul Cooke]

Thanks guys very much for the responses ..

The issue relating to what shoud be the primary key is a real difficult one to resolve for me. basically we have nurses , medics working at various locations throughout the day. they only deal with "patients" if the patients suffers an injury or accident whilst at work. the nurse would treat the paitent and then commpllete a paper treatment form on the location. this form then eventully comes back to the office where it will be entered onto the DB for future reference.

Without wishing to insult the intelligence of the people we work with ! most would not have a clue what their NHS number is (I wish they did as it would make this project a lot eaiser !) to be honest I'm pretty much on the ball with 'personal admin' and I don't know what my NHS number is - which is why i considered the postcode option (as most people know that) but I take all your points on the various issues on using this as a primary key and will get my thinking cap back on.

Paul .. just for clarification I would have one form (called New Treatment for example) when that form opens it will have a combo box on it that allows me to select a particular patient, if that patient has never been seen by us before it would trigger the Not In List event and a new Patient Record form would open allowing us to enter the patients personal details, once that form is completed it would return to the original treatment form to carrying on entering the information relating to the new treatment?

Thanks again for all your help and advice

Paul

 

[pbaldy]

Paul .. just for clarification I would have one form (called New Treatment for example) when that form opens it will have a combo box on it that allows me to select a particular patient, if that patient has never been seen by us before it would trigger the Not In List event and a new Patient Record form would open allowing us to enter the patients personal details, once that form is completed it would return to the original treatment form to carrying on entering the information relating to the new treatment?

Yes, exactly. There are other ways, but using the not in list event is a common one.

 

[Paul Cooke]

Thats great Im onto the design of the tables / forms now I've gone for Autonumber as the key ! - hopefully it will be plain sailing

Is it ok if I need to post back on this topic?

Thanks again for all the advice.

 

[pbaldy]

Of course you can post back. One of us is usually around.