Hey all,
I have played around with my DB design to the point i feel i am digressing into not even being productive so i need some help with composite keys to establish a relationship between to tables.
First, my entire DB consists of excel file extracts that i do monthly. Because the data has so many attributes, the site i extract from (and access) forces me to do it in two extracts. Essentially what i have are two tables that have the exact format of how i extract my monthly file for the sake of easy importing. This is creating problems (well at least for me cause i'm a rookie). Unfortunately for this reason i am pretty strapped on how i design my tables because i am constantly importing from these excel files that mandate the columns be in the same order and everything (unless of course theres a better way?)
The tables contain data for heart attack patients. Record one of both tbl1 and tbl2 contain data for the SAME patient. Essentially, it is like a flat file that is split into two extracts, therefore two tables. So record one for "John Doe" has like 240 attributes in tbl1 and then 200 more for the same John Doe in tbl2. Autonumber won't work as i have found instances where the site i extract from transposes records. However, I have two fields i have identified as unique that both tables have; one is an patient ID number, one is an admit date. There are instances where one patient will have multiple heart attacks in their life, but we have never seen an instance where a patient has been admitted twice in one day for a heart attack (especially considering we can't discharge them from the hospital that fast). These two fields would work, but i'm having a heck of a time convincing myself this is the best way.
Secondly, if i do decide to go this route. I'm honestly not even sure how to establish a composite key relationship in access. Do i have to establish it in the "Relationships" design by linking ID to ID then AdmitDate to AdmitDate? Forgive me if this seems extremely basic. See below how i have it currently.
Forgive my rookie-ness, i just want to make sure i get this right. Thanks!