De-identified, external data trickiness

[uppertoe]

I am writing a database for a small scale research project, and there's a requirement that patients involved must be stripped of identifying data. The data will be stripped prior to data entry, and kept externally in an excel file.

I plan to have an external excel file which lists for each patient:
PatientHospital_ID (must not be stored in the database)
PatientAnon_ID (a random number which can be stored in the database)
PatientDOB etc

The main tables in the database will include:

tblEncounterDetails: Encounter_ID (primary key), Patient_ID, EncounterDate, EncounterType etc
tblPatientDetails: Patient_ID (primary key), PatientAnon_ID, PatientAge, PatientSex etc

Each patient can have multiple encounters.

I have two questions:

1)
I would like the main data input form to have a field for typing the patient's Patient_HospitalID (which is not stored in the database). Upon pressing enter/a button, I would like a query to compare this input to the PatientHospitalID in the external excel file, and return the corresponding PatientAnon_ID. If there is more than one matching PatientHospitalID (this is possible), I want only the first PatientAnon_ID returned



Then, I would like this PatientAnon_ID compared to records in tblPatientDetails. If there is a match, the corresponding Patient_ID should be recorded in tblEncounterDetails. Otherwise a new record should be created in tblPatientDetails and the new Patient_ID should be recorded in tblEncounterDetails

2)
Would a better approach be to just take the PatientAnon_IDs from the excel file, copy them into tblPatientDetails and use it as the primary key? I'm just uneasy about not using autonumber for a primary key.

Thanks very much, and sorry for the convoluted question!

[June7]

Is the Excel file structure simple enough to allow linking?

Are you saying there could be multiple PatientAnon_ID values associated with the same PatientHospital_ID?

[uppertoe]

Are you saying there could be multiple PatientAnon_ID values associated with the same PatientHospital_ID?

Unfortunately yes. Preferably this could be filtered out in access automatically, but if need be it can be done by hand in excel.

The excel structure is very simple and can be linked.

[June7]

Linked Excel can be used like a table - except cannot edit the spreadsheet data through the link. The link can be joined to other tables in query or can use DLookup() domain aggregate function to pull data from the linked data.

A link would be the simplest method to utlilize the spreadsheet in Access.

Access cannot automatically filter out the additional PatientAnon_ID values. However, queries and domain aggregate functions (DMax, DMin) could target one - perhaps always the minimum value. Otherwise, clean up the spreadsheet dataset.

[ranman256]

Autonumber is a perfectly fine method for primary keys.

[uppertoe]

Thanks for the advice. It sounds like a link will be an easy way to do this. I will clean up to source data manually I think - as you say it's just asking for problems with duplicate records.

How possible do you think it will be to 1) find records in tblPatientDetails that have a matching PatientAnon_ID, 2) insert the corresponding Patient_ID into the record for tblPatientEncounter, and 3) if no matching record exists in tblPatientDetails, make a new record, set PatientAnon_ID to the inputted value then insert the new Patient_ID into tblPatientEncounter?

My feeling is that this would be much, much easier if PatientAnon_ID were just the primary key for tblPatientDetails - but it could also be quite limiting given that it's not an autonumber.

ranman256 I'd prefer to use autonumbers for the primary key. I guess I'm worried that if I use pasted data from excel instead of autonumber, adding new records will be a very manual process.

Cheers

[June7]

What is the common identifier for associating Excel data to Access data - PatientAnon_ID?

All that can probably be done with a series of queries.

[uppertoe]

Yes that's right, PatientAnon_ID will be the common link between the excel file and access

[June7]

Okay, link worksheet and start building queries. When you encounter specific issue, post question.