Results 1 to 9 of 9
  1. #1
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143

    Help with Tables and database design with primary key and foreignkey with missing values

    Dear Experts,

    I have an excel survey form (attached) that I would like to import into Access and create forms for the users to review their information and at the same time enter the new data.
    Challenging part: Top section is always complete where as in the PCR Instrument section; some time there will be one or two entries only but I want to show all of the 5 options in the form.


    Similarly NA extraction section also has 7 lines for options but one or 2 lines are filled.

    Should I create one big table or should I split the data into small tables? If 3 small tables what is the best key to link and the three sections. For on big table I used UnitId as unique key.

    I have about 6 survey forms with different data elements, that I need to import into access DB and use access forms to edit and set up queries and reports in future. Any help is much appreciated.

    Thank you
    Attached Files Attached Files

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    In Access you would structure this so that you have 1 table for the Top Section
    Then a Table that deals with PCR Instruments details.
    And a 3rd Table to deal with the NA Extraction details.

    This is a standard Main Form for the Top Section Values then 2 subforms to deal with the related PCR & NA values.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Thank you for the advice. should I use UnitId as the primary key in all the 3 tables to link them for queries? or should I use the autogenerated key

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You would use UnitID as the Primary Key - Autonumber for the Top Section

    Then each of the other 2 tables would have their own Autonumber Primary Key and a Foreign Key of UnitID to link to the Parent Primary Key UnitID
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  5. #5
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also needed would be tables for the Units, Contacts, Instruments, Manufacturers.
    Consider
    Click image for larger version. 

Name:	Relationship1.png 
Views:	20 
Size:	74.8 KB 
ID:	43723

    In tblSurveys, who/where does the email and phone belong to? Unit, Contact, Secondary Contact?


    Note: this is just a suggestion of how I would start this dB.....
    Attached Files Attached Files

  6. #6
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Created main and sub form and linked by unitID. In the subform instead of showing all the 6 records on one sheet it's showing repetition of one record 6 time on 6 sheets. Failing when trying to create the relationships and enforce referential integrity between the main the PCR tables. Any thoughts how to fix this issue?

  7. #7
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    Steve, just saw your relational DB diagram. It will be too complicated for this project. We might receive at the most 30 survey responses so I just want to keep this simple. This project is for2-3 users who want to know what are all the instruments in each unit.
    Thank you taking time for explaining the normalized tables.

  8. #8
    stalk is offline Competent Performer
    Windows 10 Office 365
    Join Date
    May 2014
    Posts
    143
    It worked when I explicitly choose "Link Master Fields". Somehow linking did not happen when used the Wizard.
    Thank you both for spending time and giving me solution.

  9. #9
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Glad to help
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Replies: 3
    Last Post: 11-10-2017, 05:35 PM
  2. Replies: 9
    Last Post: 07-19-2017, 11:01 AM
  3. Replies: 3
    Last Post: 04-18-2017, 02:28 AM
  4. Replies: 2
    Last Post: 10-23-2014, 08:11 AM
  5. UNION two tables with same primary key values
    By carillonator in forum Queries
    Replies: 1
    Last Post: 02-02-2010, 08:54 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