Table setup confusion

  • Thread starter Thread starter maarkr
  • Start date Start date
M

maarkr

I'm having trouble with something that should be simple:

I have a project with committees for each project. I want
to be able to select people from a db to have a list of
many people (a committee) for each project.

Project db
ProjID PK

People db
People ID PK
Proj ID FK

I want to select people from a cb on the project form and
list them on the project form in a datasheet.

Do I need another db to save my selections (select from
people and save to Committees)? Do I need a many-many
relationship setup?

thanks
 
In Access, you work with "tables" as a place to store data. DB's (or
databases) refer to the entire Access file, not just the table.

It sounds like you have Projects, Committees, and Persons.

And it sounds like you have a one-to-many relationship between Projects and
Committees. That is, one Project can have many Committees, but any specific
Committee is only related to a single Project. If true, you have:

tblProject
ProjectID
... (other Project-only info)

tblCommittee
CommitteeID
ProjectID (foreign key, pointing to the 'parent' Project)
... (other Committee-only info)

Then, you have Persons. Are you saying that a Person can only serve on a
single Committee, or can one Committee have many Persons, and one Person
serve on many Committees? If the latter, then you have:

tblPerson
PersonID
... (other Person-only info)

and

trelCommitteeMembership
CommitteeMembershipID
CommitteeID
PersonID
... (other info about a person's membership -- e.g., startdate,
termofoffice, ...)

Or have I misunderstood?
 
I was trying to make it simple by selecting people from a
cb in the 'project' form and listing those people in a
subform. I really didn't want to use a
separate 'committee' tbl unless I needed it to store the
people that I selected in each project...am I still being
confusing?
 
Typical relational database/application design starts with identifying the
"entities", their "attributes", and the relationships among them. My
response assumed you wanted to start at the beginning.

Your reply suggests that you already have a form designed, and want to try
to make tables fit your screen display. If so, you're likely to run into a
lot of work forcing Access to do something it wasn't designed (well) for.

If you want to make maximum use of what Access can do, step back and
consider looking into "normalization".

I don't believe you are being confusing, but I do suspect you have already
ruled out doing the work you'd need to in order to use Access, rather than a
spreadsheet or some other tool.

Best of luck

Jeff Boyce
<Access MVP>
 
Back
Top