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:

    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)

    The table for the non-programs looks like this:

    Telephone (y/n)
    Email (y/n)
    Fax (y/n)
    CallerType (lookup table)
    TypeOfCase (lookup table)

    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:

    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)

    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
    Rob S, Aug 4, 2011
    1. Advertisements

Ask a Question

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.