Import Excel file into 2 tables, maintain relationship

[uppertoe]

Hi there,

I was wondering if someone could lend some advice to designing a database which uses some information from a excel file.

I'm pretty new to access and I'm struggling to get my head around this particular problem; there are a few other threads that almost but not-quite answer my question. I have a feeling the answer lies in an update query but I'd appreciate any further pointers.


I am trying to ease data collection on patients who had an operation.
I have 3 main tables:


Booking episode:
-Booking ID (primary key)
-Patient ID
-Operation ID
-Booking date


-etc etc


Patient details:
-Patient ID (primary key)
-Patient's medical resource number (MRN)
-Age
-etc etc


Operation type
-Operation ID (primary key)
-Specialty
-Operation name


So that's pretty easy.


What's tricky is that I have data from an external source in Excel:
-External booking number (each patient may have more than one)
-Patient MRN (unique to each patient)
-Booking date
-etc etc


What I would like to happen during data entry:
-A list showing the external bookings which don't yet have a corresponding entry in the 'booking episode' table; this should reduce as each entry is made until all are done
-An easy way to create a new entry in the 'booking episode' table from this list, automatically inputting values such as operation date
-The simultaneous creation of a new entry in the 'patient details' table including the automatic input of the patient's MRN (if another entry doesn't already have the same MRN)


TL;DR - How to take data from an excel file, split a value into one table and another value into another table while maintaining the relationship between the two.


If I have made things more complicated than they need to be feel free to tell me to start from scratch!


Regards,


Uppertoe

[orange]

What data is in the excel file(s)?

Do not use a naming convention that allows embedded spaces or special characters in field names.
Use only alphnumeric and "_" (underscore).

Identify the business facts of your proposed database.
eg.
1 Patient can have 1 or many Bookings
1 Booking relates to 1 and only 1 Patient
A Booking may be for 0,1, or Many Operations
... this sort of thing that reflects your conditions.

Create a data model based on the business facts.
Create some test data and scenarios and makes sure the evolving data model
supports your business facts. (can be done with paper and pencil)
This is the blueprint for your database; now build the database structure.

For the excel data, I'd create a temp table to receive the data.
Do some validation of the raw data; adjust as necessary.

Use queries and data from the temp table(s) to populate the "real" tables in your database.

Good luck.

[uppertoe]

Great advice, thank you very much.