normalizing and creating fields or related tables

Discussion in 'Access Table Design' started by Rob S, Aug 4, 2011.

  1. Rob S

    Rob S Guest

    I work for a nonprofit that provides support to other nonprofits and
    gov't agencies. We use two old access databases to keep track of
    statistical data used for reporting purposes. These databases were
    created in Access 2003 or an even older version and are clunky and
    confusing for users. I am trying to create a new db that combines the
    two older db into one that is easy to use.

    As an example, one piece of data that is recorded is assistance
    provided to a caller. In our current set up we have a table that
    holds data for member programs and one that holds it for "other
    organizations" which includes gov't agencies. The data in both is
    very similar, though the screen for members collects a bit more
    detail. I believe that it is possible to combine the tables into one,
    but I'm having trouble figuring out what this would look like.

    Members are divided in my mind into 3 types, though in the old db
    being used they are not really distinguished, and for ease I will just
    use them as 1,2, and 3 and they are in the ProgramType field below.
    The "member support" table looks something like this:

    Tbl_ProgramTA
    requestDate
    Staff
    Telephone (y/n)
    Email (y/n)
    Fax (y/n)
    RemoteSupport (y/n)
    ProgramName (lookup to table)
    Topic (lookup to table)
    ProgramType (lookup to table, though I can't see where this is
    used in any report)
    TypeOfCase (lookup to table)
    Notes

    The table for the non-programs looks like this:

    Tbl_OtherOrganizations
    requestDate
    Staff
    Telephone (y/n)
    Email (y/n)
    Fax (y/n)
    CallerType (lookup table)
    TypeOfCase (lookup table)
    Notes

    Program name from the first table could be seen as being very similar
    to CallerType in second table. ProgramType in the first table, while
    not used as near as I can tell, could be used to make data entry
    easier (I think). This ProgramType really is a defining quality of
    the ProgramName field.

    So what I would like to try to do is combine the table that
    ProgramName refers to with the table CallerType looks to and add a
    column to the new table that uses ProgramType to differentiate between
    them. The notion then is that if you select type 1, it only pulls up
    the names that have been linked to type 1; similarly, when you select
    type 2, it only pulls up the organization names that are associated
    with type 2; and so on. An ProgramName/CallerType (in the old db) is
    only associated with a 1, 2, 3, or 4 and never a combination of them.

    So my question is, does this help to normalize the table by reducing
    tables with duplicate information? The new field "Organization Name"
    that combines the fields from the two old tables is a field that will
    repete, so that seems to defeat normalization as I understand it. Do
    I need a keep this recurring info in the one table? or do I have a
    new table that links the eventID to the OrganizationID? As I see it I
    think it would look like this:

    tblTAContact
    ID
    Date
    Staff
    Phone (y/n)
    Email (y/n)
    Fax (y/n) (probably can delete this)
    RemoteAssistance (y/n)
    CallerType (1,2,3, or 4)
    OrganizationName (lookup that shows a list based on whether it is
    a 1,2,3, or 4 above)
    Topic (only necessary if 1,2, or 3)
    TypeOfCase
    Notes

    tblOrganizationName
    ID
    OrganizationName
    OrganizationType (1,2,3 or 4)

    am I on the right track? is it possible to pull up a list of
    organizations that are type 1 when CallerType is set to 1 and so on?
    Would "Topic" be a separate table that is attached to the tblTAContact
    ID field since it is only necessary for types 1,2, or 3? Or would you
    leave it in the same tbl and "hide" it in the form (if possible) until
    CallerType is set to 1,2, or 3?

    Sorry for the length of this, I don't know of a short way to explain
    what I'm trying to do. There are several duplicated tables like this
    and this just seems easier to control the data entry and query for
    reports.
     
    Rob S, Aug 4, 2011
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Fred Thomas

    Normalizing for statistical analysis

    Fred Thomas, Oct 17, 2003, in forum: Access Table Design
    Replies:
    8
    Views:
    107
  2. Jeff

    Trouble importing and normalizing tables

    Jeff, Dec 5, 2003, in forum: Access Table Design
    Replies:
    3
    Views:
    143
    Jeff Boyce
    Dec 9, 2003
  3. Steve Newton
    Replies:
    2
    Views:
    93
    Steve Newton
    Aug 16, 2004
  4. Normalizing Table - is this right?

    , Mar 26, 2005, in forum: Access Table Design
    Replies:
    3
    Views:
    85
    abqhusker
    Mar 30, 2005
  5. Sydious

    Normalizing Tables Help Needed.

    Sydious, Aug 25, 2006, in forum: Access Table Design
    Replies:
    2
    Views:
    145
    Sydious
    Aug 29, 2006
  6. Pamela
    Replies:
    1
    Views:
    151
    KARL DEWEY
    Jul 10, 2008
  7. BruceM

    Related records depend on other related records

    BruceM, Oct 30, 2008, in forum: Access Table Design
    Replies:
    2
    Views:
    183
    BruceM
    Oct 31, 2008
  8. DuWayne O'Neal

    Normalizing Tables

    DuWayne O'Neal, Apr 11, 2012, in forum: Access Table Design
    Replies:
    4
    Views:
    543
    Dan Dungan
    Apr 13, 2012
Loading...