nomination table design

[newbietoo]

Hello,
I need advice on how to set up related tables to track annual nominations
for president. Data collected include nominee personal data, total hours of
volunteer work with types of volunteer work, list of nominator, and
nomination status. Records will be kept active to query historical
nominations for a particular person.
Question:
Would I create 7 tables?
1) Nominee personal data
nomineeID (primary key)
Fname
Lname
City, etc.
2 Nominators
nominatorID (primary key)
fname
lname
3) Nominations (person can have multiple nominators)
nomineeID
nominatorID
year
4) Types of Volunteer work (note: enable end-user to add record)
VolunteerType
5) Nominee Volunteer Info
nomineeID (foreign key)
year
TotalVolunteerHrs
5a) subdatasheet - list of VolunteerType
7) Nomination Status
Nominee ID (foreign key)
Year
Approved

Would it be possible to create one data entry form for the above tables?
Thanks in advance for any suggestions!
Lynn

 

[tina]

comments inline.

Hello,
I need advice on how to set up related tables to track annual nominations
for president. Data collected include nominee personal data, total hours of
volunteer work with types of volunteer work, list of nominator, and
nomination status. Records will be kept active to query historical
nominations for a particular person.
Question:
Would I create 7 tables?
1) Nominee personal data
nomineeID (primary key)
Fname
Lname
City, etc.
2 Nominators
nominatorID (primary key)
fname
lname

can a person be a nominee OR a nominator, or both? if so, suggest the
following table, as

tblPeople
PersonID (primary key)
FirstName
LastName
<other fields that describe a person - but NOT that person's current status
as nominee and/or nominator>

3) Nominations (person can have multiple nominators)
nomineeID
nominatorID
year

since one nominee may have multiple nominators, you need two tables here:

tblNominations
NominationID (primary key)
PersonID (foreign key from tblPeople)
NominationYear (do NOT use "Year" as a field name, it's a Reserved word in
Access)
Status

note that adding a Status field to this table, to track the status of each
nomination, removes the need for the "7) Nomination Status" table you
describe below.

tblNominators
NominationID (foreign key from tblNominations)
PersonID (foreign key from tblPeople)
(these two fields can be used a combination primary key for this table, or
you can add a third field - probably Autonumber data type - as the primary
key field)

the tables relationship is

tblNominations.NominationID 1:n tblNominators.NominationID

4) Types of Volunteer work (note: enable end-user to add record)
VolunteerType

looks okay.

5) Nominee Volunteer Info
nomineeID (foreign key)
year
TotalVolunteerHrs
5a) subdatasheet - list of VolunteerType

well, you lost me here. a "subdatasheet" is a separate table. you probably
only need one table here:

tblVolunteerism
ID (primary key)
NominationID (foreign key from tblNominations)
VolunteerType (foreign key from tblVolunteerTypes)
VolunteerHours

this will be a child table, related to parent table tblNominations as
the -many side of a one-to-many relationship. since the year is stored in
tblNominations, don't put a year field in this table.

this table will have one record for each nomination/volunteer type, so if
Joe Smith worked x hours for three different volunteer types in 2006, there
will be three records for Joe Smith for the 2006 nomination, each one with a
different volunteer type and the total hours for that type.

you can use various methods to calculate the total volunteer hours (of all
types) for a given nomination, you don't need to store that value in a
table.

7) Nomination Status
Nominee ID (foreign key)
Year
Approved

this table is unnecessary; see comments above.

Would it be possible to create one data entry form for the above tables?

no. but you can use a main form, based on tblNominations, with a related
subform for tblVolunteerism and another related subform for tblNominators.
tblPeople will be used in the RowSource for combobox controls on the main
form for Nominee, and in the related subform for tblNominators; you can
write code allow the user to open a separate form to add a person to
tblPeople, on the fly. tblVolunteerTypes will be used in the RowSource for a
combobox control on the Volunteerism subform; you can write code to allow
the user to update this table on the fly as well, probably without having to
open a separate form.

hth

 

[newbietoo]

Wow, thanks Tina, for the quick and most helpful response! If a nominator
can be an organization instead of a person, I would need another table, but
I'm not sure how to incorporate it into tblNominators:
tblOrganization
OrganizationID
OrganizationName

If I want to assign a mentor(s) to the nominee, would I create another child
table related to parent tblNominations. The mentor can be a nominator.
tblMentors
NominationID (foreign key from tblNominations)
PersonID (foreign key from tblPeople)

Thanks so much for your help,
Lynn

 

[Allen Browne]

You can use a generic "client" table that handles both individuals and
corporate entities. This table would then be the source of nominators,
nominees, and so on, i.e. the other tables would have multiple foreign key
fields that link back to this main table.

If that concepts sounds outlandish, there's an example database that
connects individuals and corporate entities like that here:
People in households and companies - modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[tina]

you're welcome
no, don't use a separate table for organizations. stick with one table for
individuals and organizations (i wouldn't call it tblPeople, though ),
as Allen says. the link he posted for you gives an excellent explanation of
how to handle it; suggest you bookmark his website because you'll find a lot
of help on a lot of Access issues there.

re your Mentors question: if one nomination may have many mentors, then
yes, a separate table. the tblMentors you posted looks fine. though, hmm,
essentially it's the same as tblNominators in structure. i wonder if perhaps
the two tables should be combined, with a third field to indicate whether
the person/organization involved is a nominator or a mentor. Allen, what do
you think?

hth

 

[Jeff Boyce]

Pardon my intrusion...

If an entity can be a person one time, a company another, a mentor a third
time, a ..., then it might be useful to come up with another table, a
junction table, to hold the "role" the entity is playing... (and a table
that holds roles).

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[newbietoo]

Tina, Allen,
here's where I'm a bit unclear. If the names/orgs in the generic "client"
table can assume each role (nominee, nominator, mentor) in different years,
does it make sense to define each person/org in a particular role? Also, if
I need to query all nominees for a particular range of years by nominators,
will I be able to do this?
Thanks!
-Linda

 

[tina]

If the names/orgs in the generic "client"

table can assume each role (nominee, nominator, mentor) in different years,
does it make sense to define each person/org in a particular role?

no it doesn't, and you don't need to. use the junction table, tblNominators,
to assign the person(s) and/or organization(s) who are related to a
particular record in tblNominations. Allen's setup for handling "clients" is
excellent, but in your particular situation the tblGroups does not apply.

btw, having not heard an opinion from Allen re putting nominators and
mentors in one table, i'm going to go ahead and suggest that you do that. i
can't really come up with a good descriptive name for the table...hmm,
perhaps tblParticipants, since both nominators and mentors "participate" in
the nomination process. well, i'll go with that name for purposes of
discussing the table; and going with tblClients for the people/organizations
table, the table structure would be

tblParticipants
NominationID (foreign key from tblNominations)
ClientID (foreign key from tblClients)
(these two fields can be used a combination primary key for this table, or
you can add a third field - probably Autonumber data type - as the primary
key field)
ParticipantType (the value in this field will be either nominator, or
mentor, for each record - depending on how the client is involved in the
nomination.)

in your tables design, tblParticipants will replace both tblNominators and
tblMentors.

Also, if
I need to query all nominees for a particular range of years by nominators,
will I be able to do this?

yes, by linking tblNominations and tblParticipants in a query, and setting
criteria on the tblNominations.NominationYear field, the
tblParticipants.ClientID field, and the tblParticipants.ParticipantType
field.

hth

 

[newbietoo]

Tina,
I'm trying to incorporate some of Allen's idea used in his Human app. I
like the way he created two separate forms (one for individual and one for
corp). However, I'm collecting data for volunteerism (subform) for the
nomination year and one person can have multiple nominations by individual
and by org., so I don't believe it'll work for me.

Is it possible to create a subform with a line that looks like:
Nominators: FirstName LastName OR Organization
with the following controls -
1) Lastname is a combobox to tblClient (based on MainName and
IsOrganziation=no)
2) Department is a combobox to tblClient (based on MainName and
IsOrganization=yes)
3) If new org entry, set IsOrganization=Yes, and prevent entry in LastName
4) if new people entry, set IsOrganization=No, and prevent entry in
Organization

I know Allen's design is foolproof so that a nominator would clearly be
either a person or an org., but I don't know how else to get around this.

I also like the tabs idea in his Human app, which I assume are subforms. I
was thinking of of creating one form with 3 tabs: nominee data, nomination
(iincl. subform for volunteerism, nominators), status/mentor.

I'd like to know if I'm headed in the right direction before I start coding.
What do you think?
Thanks!

 

[tina]

comments inline.

Tina,
I'm trying to incorporate some of Allen's idea used in his Human app. I
like the way he created two separate forms (one for individual and one for
corp). However, I'm collecting data for volunteerism (subform) for the
nomination year and one person can have multiple nominations by individual
and by org., so I don't believe it'll work for me.

Is it possible to create a subform with a line that looks like:
Nominators: FirstName LastName OR Organization
with the following controls -
1) Lastname is a combobox to tblClient (based on MainName and
IsOrganziation=no)
2) Department is a combobox to tblClient (based on MainName and
IsOrganization=yes)
3) If new org entry, set IsOrganization=Yes, and prevent entry in LastName
4) if new people entry, set IsOrganization=No, and prevent entry in
Organization

without testing it out myself, your description sounds workable to me. make
sure that both combo box controls are bound to the same ClientID field, of
course. and, btw, in re-reading my previous post on a design for
tblParticipants, i noticed a potential flaw: if a single
person/organization can be both a nominator AND a mentor for a single
nomination record, then the two-field primary key will not work. so if
that's the case, either use all three fields (NominationID, ClientID,
ParticipantType) as a combination primary key, or add an Autonumber field to
serve as the primary key.

I know Allen's design is foolproof so that a nominator would clearly be
either a person or an org., but I don't know how else to get around this.

I also like the tabs idea in his Human app, which I assume are subforms. I
was thinking of of creating one form with 3 tabs: nominee data, nomination
(iincl. subform for volunteerism, nominators), status/mentor.

once your tables/relationships are set up correctly, you can set up the
forms any way you choose, as long as those relationships are supported. the
"best" setup for a given situation depends on the results of your process
analysis: if the process is focused on nominations, then *probably* your
main data entry form should be based on tblNominations, with additional data
about nominations (tblParticipants) in a subform. "supporting" tables
(tblClients), that are used to feed combo box controls on data entry forms,
may be accessed many ways, depending on what best fits into the process
flow. a supporting table that has new records added to it frequently, or in
"groups" (a whole pile of client sheets that the user wants to enter at one
sitting, for instance), might work best as a separate subform (not linked to
the main form), perhaps on a separate tab page on the main form. or it might
be best to add data to a supporting table from a combo box control's
NotInList event, either programmatically or by opening a pop-up form that is
bound to the supporting table.

the key here is to create forms that support the "flow" of the process that
the database is built to support. you can think of it this way:
tables/relationships are about the *data* and how it's related in the real
world situation you're dealing with. forms are about the *people* who work
with the data - how it comes to them, and what they do with it. and while
we're at it, reports are also about the *people* who work with (or review)
the data, and what information they need to extract from it. if you approach
your database building with those compass points to guide you, you should be
always headed in the right direction.

hth

 

[newbietoo]

Tina,
I like your advice on the Nomination being the main form. Thanks! Is the
following possible before I spend time figuring out how to do it:

Create blank frmNomination for data entry; however, if ClientID for the
specific NominationYear exists, display data (including subforms) for
updating without creating a new record. Do I need another primary key
consisting of ClientID(tblNomination) and NominationYear(tblNomination)?

Main form uses qryNomination (includes all fields from tblNomination, all
fields from tblClient except for ClientID, all fields from tblParticipant,
all fields from tblVolunteerism)

Main form contains Nominator subforms (ParticipantType=Nominator,
IsOrg=False) and (ParticipantType=Nominator, IsOrg=True)

Nominee:
cboClientID contains ClientID (tblClient), lastname(tblClient),
firstname(tblClient)
Data source = qryNameForCombo based on tblClient where IsOrg=False
Bound column = ClientID(tblNomnation)

If ClientID(tblClient) exists
automatically fill in lastname, firstname
Else if ClientID(tblClient) doesn’t exist, open new form to enter client data
Go back to cboClientID
End if

Nomination Year
If both ClientID(tblNomination) and NominationYear(tblNomination) exists
Display record, including subforms and data on page 2 of tab control
THE QUESTION IS HOW TO DO THIS AND SAVE CHANGES? Is this possible or am I
going about this wrong?

TIA!