Break the problem into three components: defining the work categories,
cleaning up the existing data, and assigning the people to categories
(... or categories to people...)
First, the work
You should make two tables. Each has an autonumber Primary Key, and
each has a "description" field. One table for the general categories,
and one table for the specific opportunities.
tblWorkCategory
- WorkCategoryID autonumber, PK
- WorkCategoryDescription text
tblWorkSpecific
- WorkSpecificID autonumber, PK
- WorkCategoryIDfk long Integer, foreign key to WorkCategory
- WorkSpecificDescription text
These tables are related one-to-many -- each WorkCategory can have
many WorkSpecifics. Ultimately, you will assign each person with one
or mor WorkSpecificID's
Two - Clean up the existing database. You will need to do some of this
manually, and perhaps some with the Find Duplicates query wizard. I
would also copy the structure into a new table, then add an autonumber
field named PeopleID, then import the existing data.
Three - assign people to work specifics. I imagine that you will want
a many-to-many relation between these two tables in order to allow
each person to assume more than one responsibility. Accordingly, you
require a junction table.
tblPeopleWorkSpecific
- PeopleWorkSpecificID autonumber, PK (optional, but my preference)
- PeopleIDfk long Integer, foreign key to people table
- WorkSpecificIDfk long integer, foreign key to WorkSpecific table
Create a composite, no duplicate index on PeopleIDfk and
WorkSpecificIDfk to prevent one person from being assigned twice to
the same responsibility.
A for entering the data, the conventional way is to create a form for
people, with a subform for their responsibilities, based on the
tblPeopleWorkSpecific table. You will enter one or more
WorkSpecificIDfk for each person using a combo box whose recordsource
is driven by tblWorkSpecific.
I am trying to put together a volunteer database for my church. There are
several catagories for volunteer work, ie., Fellowship, Building & Grounds,
Worship, etc. Within each of these catagories, there are several specific
volunteer opportunities. For example: Fellowship has: Serving meals,
Greeting, New Member Sponsors, Harvest Feast, etc.
Although I know the basic of Access, I cannot seem to figure out how to
structure this data base into tables and how to link them to the master file,
which has no primary key as there are duplicates and blanks spaces in some of
the items.
Any suggestions?
**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
