How to split an excel sheet with all data into a one-to-many relationship?

[kawi6rr]

I’m trying to create a database to track melanomas. I need to split the excel sheet into a one-to-many relationship because one member can have multiple melanomas.



Tables
memberInfo ß---- one-to-many ------à melenama

meberInfo structure

Name Type Size
id Long Integer4
MRN Long Integer4
Last Text 50
First Text 50
MI Text 50
Sex Text 50
DOB Date/Time 8

Melanoma structure

Name Type Size
melanomaID Long Integer 4
MRN Long Integer 4
ProvLast Text 255
Prov FirstText 255
ProvMI Text 255
Degree Text 255
PCP FirstText 255
PCPLast Text 255
PCPClinic Text 255
LastSeen Text2 55

I have multiple excel sheets that I need to import but haven’t been able to figure out how to get these split and yet keep referential integrity.

Any help is appreciated, thanks!

[NTC]

Which field is the common (key) field that cross references between the 2 tables?

[kawi6rr]

The MRN number can be a unique identifier but I usually let Access supply the ID. But looking at those tables in the attempt it looks like I was using the MRN number at the ID.

[NTC]

Your design looks correct. Use the MRN as the cross referencing field. You can still add the primary key from Access field too - to each table. The MRN does not have to be the primary key - but it is important in the Member table that the MRN never repeats.

As your post is emphasizing "split the excel sheet" - I am guessing all this info exists in 1 excel sheet today - meaning that a member with multiple melanoma has multiple record entries.

Import the whole excel sheet into a table - name it Member. Then duplicate the table - name it Melanoma. Then delete all the inappropriate columns off each respectively. Finally you want the Member table to contain only 1 record per member - if the volume is not onerous then just sort on member name and manually delete duplicates; otherwise if the volume is high you will need to create a one time action query to do this.

Then go to Relationships and draw a relationship from Member MRN table to Melanoma MRN table.

Hope this helps.

[kawi6rr]

This worked, Thanks!!