Table relationship question

[scoughlan]

Hi. I work in a hospital and have created a database to keep departmental treatment records for our patients. I have a treatment record table; which is a series of dates and text fields that represent treatment interventions and the healthcare professionals involved in a patient's care. Joined to this is a patient table which contains the default autonumber field, patient lastname, patient firstname, and hospital number. The hospital number is a unique identifier and I have set this to prevent duplicates. However, I have a whole year's worth of data with no hospital number, transferred from an Excel spreadsheet. I would like to ask what is the best way to avoid duplicate patient data entry in this situation. Each treatment record can only have one patient, but each patient may have many treatment records. At the moment, when I create a new treatment record, I can blithely create another patient entry in the patient table with identical patient names but different autonumber fields. Even more confusingly, there can of course be multiple patients with the same name! Any ideas of how to handle this situation? Thank you all.

[RayMilhon]

Which HIS does your hospital Use? (Cerner, Meditech, EPIC...) You should be able to use that data for your purposes. If you're unable to use that data then we would need to see the data structure of your database inorder to assist.

[scoughlan]

Hello, and thank you. The hospital number is local, given to patients of group of hospitals called a Trust in UK NHS-speak. I will attach a screen dump of the database relationships for you.