Results 1 to 7 of 7
  1. #1
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17

    Composite key relationship structure


    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!

    Click image for larger version. 

Name:	Capture.PNG 
Views:	19 
Size:	18.2 KB 
ID:	29336

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If this is going to be a database with data storage, entry and reporting then you need to design a proper table structure first.

    Otherwise the way you have it is correct. It seems you can select any of the first 6 fields and they should match. Do you have any provision for mismatched records?

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I avoid compound keys and have only used them once.

    Relationships do not have to be established in the Relationship builder for a db to function but doing so may be helpful when building queries. The links will automatically set when tables are brought into queries. However, there are sometimes occasions when the defined join types are not desired and manually adjusting the query is required.

    You will likely continue to be frustrated if you try to replicate Excel 'flat file' structure in a relational database design. Replicating the Excel structure may be expedient for import of data but might encounter difficulties in data analysis. A conventional relational database structure would be more like:

    tblPatientInfo
    PatientID_PK (an autonumber field), NCDRPatientNum, LastName, FirstName, DOB, Sex, Race, etc.

    tblPatientAdmit
    PatientID_FK_Admit, AdmitDate, other info related to admission

    tblPatientVitals
    PatientID_FK_Vitals, etc

    Otherwise, as aytee111 states, what you show is correct.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17
    Is there a way to break out the tables while still maintaining a smooth import process? It was being EXTREMELY finicky when i was playing around with it at first, constantly kicking me import errors that here-and-there items didn't match. For that reason i'm a little gun shy when it comes to altering the tables for the sake of importing. This DB's main purpose is querying for charts and presentations. All the data entry and storage is done online through a national DB called a "patient registry"; this is where i extract from.

    And when you say provision for mismatched records, do you mean verifying the tables are set up right and querying correctly? If so, the only way i have done that is by querying something and then manually checking the database to verify the queried data is accurate.

    Thanks for the response! I appreciate all the help.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Importing from Excel is tricky, as you have found, with not much control over the data and fields. You may want to consider an additional query of taking that data and putting it on to another table, one that you have designed and where you specify the correct data types and lengths. This can be done in VBA or a macro. It would give you much better reporting when all fields are exactly as they are supposed to be. Also, Access determines the data type from the first 8 rows of the Excel file, for instance a date field will be imported as text if the first 8 rows contain a blank. In the query you can convert fields, trim spaces, etc.

    For mismatched I was thinking of the first 6 fields that look as though they should all be exactly the same - what happens when/if they aren't?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    As a for instance, what if patient is in one record as John Joseph Smith and another as John J. Smith or no middle at all but with the same SSN? The names don't match.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rbennion is offline Novice
    Windows 7 32bit Access 2013 32bit
    Join Date
    Jun 2017
    Posts
    17
    The data in the first 6 rows is only entered once. The site I extract from has the option to extract in two separate files which they simply repopulate the first 6 rows for the sake of navigation in either file. What I mean to say is that the data is the same, just automatically repopulated by the site for the sake of extraction, so mismatches "theoretically" shouldn't exist. Thanks for all the help guys!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table/Relationship Structure- Please help.
    By AishlinnAnne in forum Database Design
    Replies: 2
    Last Post: 03-29-2017, 10:53 AM
  2. Relationship Structure
    By buckwheat in forum Access
    Replies: 1
    Last Post: 07-12-2013, 01:12 PM
  3. Query relationship structure
    By Juicejam in forum Queries
    Replies: 15
    Last Post: 02-12-2012, 10:26 PM
  4. Schedule: Table/relationship structure
    By capnponcho in forum Access
    Replies: 1
    Last Post: 12-18-2011, 01:24 AM
  5. Structure/Relationship Question? Please Help...
    By Imgsolutions in forum Access
    Replies: 2
    Last Post: 06-27-2010, 09:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums