Make a report listing each record containing the names of members. 1

[Sherman6789]

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 to do either way. I don't know how to do what I guessed or any other method.

Any assistance you can give will be appreciated. Thanks.
WS

 

[dhookom]

Your table structure is not normalized. You should have one record per member per committee. Then you query will be simple.


Duane
Hook'D on Access
MS Access MVP