Solved

We need to make a report that finds each record containing the names of committee members.

Posted on 2011-03-22
9
395 Views
Last Modified: 2012-05-11
I've created a database which contains information about special committees in the office.  Each committee has from 1 to 5 members.  Each member is listed in field Mem1, Mem2, Mem3, Mem4 or Mem5.  Field Mem1 is usually the chairperson.  Field Mem1 and Mem2 may be co-chairs.  Fields Mem3 - Mem5 may list all other members or may be empty.  Any member could show up in any of the five fields. We need to create a report that lists the members and shows which committee they serve on. We have no trouble making a report the shows the lead person and all other member working with the lead.  We need a report that lists each individual and all committees the person belongs to no matter which "Mem field" he or she is listed in.

I am guessing that I need to create a "table of members" with all members listed in alphabetical order.  The program will look at the first name in the member table and go through the records one by one and compare that name to each of the five Mem fields.  If it finds that person's name in any of the five fields it will list the name at the top of the report page and also list the committee.  At the end, it will have listed all committees that that person belongs to.  If it did not find that name on any of the records, it will not list the name.  It will then go to the next name on the "table of members" and repeat the process.  When completed, we will have each person listed with all of their committees under his or her name.  There may be an easier way to do this but I don't know how. I don't know how to do what I guessed or any other method.

Any assistance you can give will be appreciated.   Thanks.
WS
0
Comment
Question by:sherman6789
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 1

Accepted Solution

by:
beakt earned 250 total points
ID: 35194735
WS,

This sounds like it shouldn't be so hard, but I'm unclear on the construction of your tables.  If they're not done right, then the query you're describing might be difficult.  If they are done right, then it should be easy.

You should have a fully relational database:  That is, a single list of each member, with a unique primary key for each one.

Then, what I would do is have another table with a list of each committee.  That table would have at least two fields; one with a unique primary key, and the name of the committee.  You could include other fields, such as meeting date/time, the room they meet in, etc.

Finally, you can make a table that links the other two.  It would only need two fields:  Each is a foreign key to the primary key in one of the other tables.  The two fields together would be a primary key.  Then you could put a third field that designates whether the person is a chair or co-chair for that committee.  You could then use logic in the front-end to limit each committee to five members.

So you might have

Members:

1 Joe
2 Mary
3 Bob
4 Charlie

Committee:

1 Finance
2 Party-Planning

Then the link table would be:

Committee/Member/Type:
1/1/Chair
1/2/null
2/2/Chair
2/3/Chair
2/4/null

So this shows that Joe and Mary are on the Finance Committee, and Joe is the Chair.  Also, Mary and Bob are co-chairs of the Party-Planning Committee, and Charlie is a member.

Is this close to how your database is set up?
0
 
LVL 44

Assisted Solution

by:GRayL
GRayL earned 250 total points
ID: 35195232
First things first.  The db design is all wrong.  

Committees
========
CommID
CommDesc
etc.

Personnel
=======
PersID
PersName
etc.

CommPeople
=========
ComPersID
ComID - fk from Comittees
PersID - fk from Personnel
etc.

This is where you should be going.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35201080
Sorry for being so abrupt.  I think we need an additional table

CommMakeup
==========
CommMUID
CommID
PosnID - Integer
PosnDesc - text such a Chairman, Co-Chair, Member etc.

Then we add a field to:

CommPeople
=========
ComPersID
ComID - fk from Comittees
PersID - fk from Personnel
PosnID - fk from CommMUID
etc.

Now it all hangs together.  
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:sherman6789
ID: 35201746

Thanks to Beakt and GRayL for your quick responses.

I am in the process of studying them both.  With my little knowledge, they both seem very similar.  What is the major difference between them?  They both have multiple tables: Committee table, Personnel table and a third table that links both of these tables.

I think that beakt's method adds additional information such as identifying the chairperson and any other information as fields in one of the tables.  GRayL's method seems to add additional table which will handle other information.  I hope that I am reading them correctly.  If I am not, please let me know, I will continue to study these suggestions tonight.

Thanks again.

WRS

0
 
LVL 1

Expert Comment

by:beakt
ID: 35202227
WRS,

GRayL's suggestion in his first post is pretty the exact structure I suggested.  It's standard relational database design.

They look different because GRayL provided the suggested names of fields, whereas I just described them and posted sample data.

Also, he included a separate primary key (ComPersID) in addition to the two foreign keys for the link table.  I lean towards leaving that out, and setting the two foreign keys as the primary key for the table, but I'm open to rationale on why a separate key might be useful.

Yes, you can put any other fields in each table to include information unique to each record; as I said, meeting place and time, shared Public Folder, etc., for a committee, and for each member, their department, phone number, e-mail address, etc.  And particularly, having the designation for each entry in the link table (linking members to committees) that designates charimanship.

In GRayL's second post, he adds a separate lookup table (CommMakeup), so you can have the text (such as "Chairman") entered only once, and then the field in the link table (which links members to committees), which designates their position, can be a simple integer.  That's a good idea, but I might make that link table (CommMakeup) just have two fields:  the integer for a position, and then the name of the position.

I hope this is making sense.  There are many tutorials on relational database design.  When I first started reading about it, it didn't make sense until I started pounding on Access, and really saw how it works when you want to put together queries.

Please experiment, and see how it goes, and let us know.

Jeff
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35202327
Thanks Jeff.  That sums it up well.  I avoid the use of compound primary keys for the reasons explained by DatabaseMX in http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26906619.html?cid=1131#a35200495:

"Highly recommend you make the Access AutoCounter the Primary Key, then - if necessary, create a Compound Unique Index on those 4 fields (easy to do).  A compound PK is a PITA, especially when create relationships based on that table and/or when you need to refer to the PK form elsewhere, say in code ... even joins in queries."
0
 

Author Comment

by:sherman6789
ID: 35202973
Thanks!  Both of you have given me plenty to study tonight.  I believe that I will be able to make it work for your suggestions.  I'll respond soon.
0
 

Author Comment

by:sherman6789
ID: 35386778
Hello,
I am back from a long company trip which caused a delay in my response.  I need to end this string because the information that I received from beakt and GRayL are excellent.  I was able to get the report that I needed.  Thank you.  I will split the point half and half.

WRS
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35388147
I hope I get the first half;-)
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question