multiple IDs

[JulieAsking]

I am new to Access 97 and have created a database for patient operations. I am now entering data on a test basis. When I add a new record for the same patient (many patients have more than one operation), the same patient is given a new ID number each time. I know I can always get a patient’s operation history by running a query on the patient’s name but it doesn’t look right to see the same patient with multiple ID numbers. I have a Patient Details table joined to an Operation Details table by an autonumber PtID key. I cannot use a patient’s medical record number as a primary key as the same patient can have operations performed at different hospitals and so can have more than one medical record number. Is there any way around this?

Also, I am using a continuous subform within a main form to enter data (as continuous allowed me to add command buttons to enter new values into the combo boxes) but ideally, I would like to be able to switch continuous to datasheet view and see all relevant operations in the subform for each individual patient's name in the main form. If each patient kept the same ID number I’m assuming it would be possible to view the data this way?

Thanks for any suggestions.

Julie

 

[Pat Hartman]

You must have a table that defines patient information. The autonumber key for this table can be used as a patientId. Then when you enter operation information, use the patientId as a foreign key.

 

[JulieAsking]

Thanks for your reply, Pat. What you have suggested is actually how I have my tables set up. I have an autonumber primary key called PatientID in the PatientsDetail table and have used this as my foreign key in the OperationDetail table.

Problem is that Access97 doesn't recognise when I am adding a duplicate value to a LastName field (eg. lots of 'Smiths')and therefore I am unable to combine the new record with a previously existing record for say, Joan Smith (as opposed to Jack Smith).

Is there a way I could introduce a message box or something that would alert me to the fact that the current data being entered into the LastName and FirstNames fields exists elsewhere in the database? and then give me the choice of ignoring the message box if, on checking, it is a different 'Smith'? If this is possible, and I found the current 'Smith' had already had an operation 3 months ago, I could then change the subform to datasheet view in order to include the latest op details with the former op details. This way, I could bring up a patient's operation history using Forms, and not having to run a query on their name. Is my thinking clear on this?

Thanks - Julie

 

[Fizzio]

A good way of doing this is by using an unique patient identifier and using a search facility on your main form to 'filter' (Using a select query) that ID. In the UK you could use the NHS number but I do not know the equivalent in the US or you could ask access to create your own eg (one I use) first 4 letters of surname, Initial of firstname, gender, DOB and autonumberid exaple SmithJM150456-123. I then use the search bux to search this field.
HTH

 

[Rich]

Julie's actually Down Under as opposed to Over The Pond so NI numbers may be relevant, assuming the're known. I prefer an unbound text box which limits a list box as I type the letters of the Last Name i.e. S, all S's Sm Smith,Smale etc, and so on.
The list box combines First,Last Name and Address, a double click on the list box opens the form with the relevant records displayed. I find it very quick.
HTH

 

[Fizzio]

Rich, that sounds an interestng way of doing it, I'd never thought of that. Would you mind sending an example or posting the code? Cheers.

 

[JulieAsking]

Thank you both Fizzio and Rich.

Rich - your description sounds like just what I need but I'm not sure how to go about this. Would you be able to spell it out in a little more detail for me please? It sounds like you are talking about two separate boxes here?

Thanks - Julie

 

[Rich]

On their way