Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Vancouver, Br. Columbia, Canada
    Thanked 0 Times in 0 Posts

    table design advice - one-to-one or not (2003)


    A colleague is working on a database for storing and querying results
    of various productivity studies. He is an Access novice, but wants to
    (mainly) build this database for himself. My initial recommendation
    was to set up three major tables for him:

    - PK StudyID - autonumber
    - about 50 fields that are common to two types of studies

    - PK StudyIDfk - longInt
    - about 50 fields that are specific to "detailed" studies

    - PK StudyIDfk - longInt
    - about 50 fields that are specific to "shift-level" studies

    A particular study site (ie, a record in Common) can have either a
    Detailed study or a Shift-Level study, or both. The database will grow
    with time, but will probably top out at about 1500 records. This will
    be a personal database -- no end-users except for my colleague.

    He ran into problems with this three-table approach. I had showed him
    how to make a single form for tblCommon, with two subforms for the two
    subsidiary tables. It worked well until he wanted to apply filtering
    and sorting to some of the fields in the subforms. Of course, that did
    not work because each subform contained only one record - the one
    related to the current record in the main form.

    Then I tried to create a single query using outer joins that displayed
    all the fields and records from all three tables. Worked well to
    display the data, but was non-updateable because of its two outer
    joins, and therefore useless for data entry.

    My next thought (not implemented yet) was to create two queries and
    two forms that dealt with (Common + Detailed) and (Common + Shift)
    data. These two forms would be accessed completely independently from
    one another, and the fact that two studies shared the same Common data
    would appear to be coincidental. Under this scenario, if my colleague
    was viewing a Detailed study and wanted to check whether there was a
    corresponding Shift-Level study, using two separate forms would make
    that process somewhat awkward.

    I guess the ultimate "inelegant" approach would be to dump everything
    into a single table, and treat it like a spreadsheet, but I have a
    hard time recommending that...

    I am leaning towards recommending the two-query, two-form approach for
    its simplicity, but it bothers me for some reason. Perhaps it seems

    Can anybody offer any insight or advice?
    Jack MacDonald
    Vancouver, Canada

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Sacramento, California, USA
    Thanked 1 Time in 1 Post

    Re: table design advice - one-to-one or not (2003)

    I don't think I quite understand. You mention a one-to-one relationship so why would he be trying to filter and sort anything? The kind of query involved isn't going to change anything in that respect. If I were setting it up, I would do it much the way you originally suggested. I suspect there is some information about this missing from your post or it would be more obvious what is going wrong. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: table design advice - one-to-one or not (2003)

    It is possible to filter a main form/subform combination based on criteria in a subform, but it requires writing non-trivial VBA code. In this situation, I would probably go for the "inelegant" approach of using only one table, even if it means that lots of fields will be empty. Your colleague could use a tab control to display the information, and show/hide the tab page for Detailed studies as needed, and same for the tab page for Shift-level studies.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts