Results 1 to 4 of 4
  1. #1
    Lori944 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Location
    Minnesota
    Posts
    2

    Connecting three tables together

    Hello -

    I'm sure there is an easy way to do this, but I can't seem to find it.

    I work in an office where we do testing with clients . I want to create a database that can create a unique report for each client on the testing results.

    I envision a database with at least three tables. The first table would be client demographic information with a unique ID field (CID). The second table would be the Appointment information (date, referral source, etc.). The third table will have the test results (although I'm wondering if I should have a table for each test).



    Sometimes, we see clients more than once, and so need the ability to have more than one appointment record for each client. For each appointment, we would record test results.

    I have created a one-to-many relationship between the Client Demographics and the Appointment tables. When I created the form for the Client Demographics, I inserted a subform for the Appointment. That works great. The CID automatically transfers to the Appointment record and instantly connects the two.

    My problem is - How do I connect the Test Results table so that the CID automatically transfers to the Test Results record as well as the Appointment Date field from the Appointment Table.

    Any help anyone can give me would be appreciated. Thanks much!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Does an appointment *always* result in a test? or do you have people not show up and/or never complete the testing phase?
    Is recording the reason they didn't show up relevant?

    What are the test results, is it a single score, or do you record different metrics for each test? for instance let's say you were giving someone an eye exam are you simply recording a pass fail or numeric score or are you, for instance, recording their color blindness and degree, then are you recording their left and right eye prescription? My point is that the recording of the test results have very different data points but if you are simply storing numeric values or domain scores that's a different matter entirely and the answer sort of determines how you would go about designing the table(s) that will store the testing results.

    In any case, in terms of data entry you will likely have a main form (based on the client), a subform (based on the appointment) and a sub-subform (based on the testing results) if you are using bound controls/forms. Because the results are bound to a specific appointment you want to enforce referential integrity through a form/subform.

  3. #3
    Lori944 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Location
    Minnesota
    Posts
    2
    Quote Originally Posted by rpeare View Post
    Does an appointment *always* result in a test? or do you have people not show up and/or never complete the testing phase?
    Is recording the reason they didn't show up relevant?

    What are the test results, is it a single score, or do you record different metrics for each test? for instance let's say you were giving someone an eye exam are you simply recording a pass fail or numeric score or are you, for instance, recording their color blindness and degree, then are you recording their left and right eye prescription? My point is that the recording of the test results have very different data points but if you are simply storing numeric values or domain scores that's a different matter entirely and the answer sort of determines how you would go about designing the table(s) that will store the testing results.

    In any case, in terms of data entry you will likely have a main form (based on the client), a subform (based on the appointment) and a sub-subform (based on the testing results) if you are using bound controls/forms. Because the results are bound to a specific appointment you want to enforce referential integrity through a form/subform.
    The appointment will always result in test scores. No-show appointments aren't relevant to our data.

    There are usually three or four tests that have scores. Each test has a variety of scores. That's why I was thinking of a table for each test. Some of the fields in the Appointments table are alpha, but as far as the testing results go, they could all be numeric as I was thinking of creating Option buttons for some of the fields.

    I'm not sure what field to connect the two with. Do I need to manually create a primary key for the Appointments table that can transfer into the Test Results table? I can't use the Date of Service as I will have a lot of clients on the same date of service so it's not unique? Can't use the CID because they may come in more than once? Any help? Thanks!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If all you're doing is recording a final score of a specific test you need:

    1. A table of your people
    2. A table of your possible tests
    3. A table of your appointments (FK to people table)
    4. A table of your test results (FK to your appointments table and FK to your tests table)

    In other words your test results table would record a person and a test (let's say you have 20 tests) the person took and their score on the test. This structure would allow you to record any number of tests on any given appointment date for any given person.

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

Similar Threads

  1. Connecting tables
    By FrankGrinds in forum Database Design
    Replies: 2
    Last Post: 05-05-2013, 07:36 PM
  2. Replies: 5
    Last Post: 02-12-2013, 03:14 PM
  3. Connecting periods from different tables - database design problem
    By sockswithsandals in forum Database Design
    Replies: 4
    Last Post: 04-15-2012, 08:39 AM
  4. connecting two tables?
    By imintrouble in forum Access
    Replies: 1
    Last Post: 01-24-2012, 02:22 PM
  5. Database design - connecting 2 tables
    By Eisaz in forum Database Design
    Replies: 2
    Last Post: 10-16-2009, 09:19 AM

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