R
Rob S
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.
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.