Formatting Question

[KBoone]

I am creating a database list of our volunteers. Some volunteers serve on
more than one committee. What would be the best way to format a database
where when you create a report it recognizes that there is more than one
committee listed in a cell? Is that possible? Any suggestions would be great!

Goal: Creating a report where I can seperate by committee and it would be
recognized that the same volunteer is on more than one.

 

[joecosmides]

I have to assume that you have a table for just the volunteers (We'll
call it Volunteertbl)

In that volunteer table you should have the followinf fields at least:

VolunteerID (autonumber/primary key)
FirstName
LastName
Phone
Address
City
State
Zip

When you assign these volunteers, you'll really be assigning there
ID's which are unique and no two are alike. when you create a query
later on you can do all kinds of stuff like show only committees that
have the same person assigned to them or show everything and then you
can sort by whatever field you want.

It's hard to give you advise this early because I have no idea what
the structure of your database will be like. The things you asked
about can easily be done but since I cannot see your database and the
info you gave is not really anything for me to go by I can;t give you
a really good answer. If you send me the database I could be a lot
more help.

 

[Dale Fye]

You have a many-to-many relationship. Many people who may belong to many
committees. Best way to handle this is via 3 tables (People, Committees,
Committee_People).

This is not Excel, this is Access. In a properly structured relational
database, you should never have more than one piece of information in a
"cell".

How you actually get the data into the 3rd table is the trick. The easiest
way to do this is to have a form that has people information, and a subform
that is linked to the main form on people, that contains the data from the
3rd table. In the subform, you would need fields for the PersonID and
CommitteeID. I would setup the Committee field as a combo box that uses the
committees table as its RowSource. Then, when the user selects a committee,
the record gets added to the Committee_People table.

If you need more help with this, post back and I'll provide more specific
guidance.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Steve Schapel]

KBoone,

The basic concept here is that you will need a separate table for
CommitteeMembership, which contains each instance of each Volunteer's
membership on a committee. You would base a continuouls view form on
this table, and this form would then be placed as a subform on the
Volunteers form. That way, you can make a separate entry for each
committee for that volunteer.

This structure will make it easier for you to do things like report on
each committee's membership, etc.

 

[Klatuu]

You need 4 tables for this.
A Volunteer table that contains one record of demographic data for each
volunteer.
A Commitee Table that contains one record of informational data for each
commitee.

Now, since there one volunteer can be a member of more than one comittee and
a commitee will comprised of more that volunteer you will have a many to
many relationship (many volunteers to many commitees). The way you resolve
a many to many relationships is with a junction table.

In this case, you need a CommiteeMembers table. It needs 3 fields.
1. A foreign key to the Volunteer table
2. A foreign key to the Commitee table
3. A foreign to the Commitee Position table.

So, that really brings us to the 4th table - the Commitee Position table.
That would be a list of the possible postions on a commitee:
Chair, ViceChair, Secretary, etc.

 

[KBoone]

That is probably where I am unfamiliar then. Is there instruction on how to
accomplish this somewhere?