Results 1 to 5 of 5
  1. #1
    dylntbrown is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2

    Membership Query

    Hello All,

    This is my first post here, I have been scouring this forum (and the internet) for a seemingly easy solution to a query I am trying to run, however I am having trouble finding my solution. I believe I'm just having trouble putting into a search term what I am trying to accomplish, so I am sure the answer is out there, but I need to move on and ask for help.

    I am the administrator of a club that sends out yearly registrations to a large group of people to request people to register for the upcoming years club. On this registration is typical information like name, company, email, etc. Also required for the registration is for the person to choose 2 (of 7) committees within the club to be a part of.

    What I am trying to accomplish is to create a list for each of the 7 committees showing the members in each committee.



    I have all of this information into Access. For the committee selections each person (or record) has a "committee1" and "committee2" field that I have populated the choices each person made.

    I am easily able to run a query where I can summarize the members in a committee, but only if I am summarizing "committee1" OR "committee2", however I want access to combine the people in "committee1" AND "committee2" into a summary of members in each committee.

    I could easily do this in excel, however at this point I really just want to use Access, so then in the future its all dynamic and every year I can populate the updated committee membership lists.

    Thanks in advance!

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What you have is known as 'repeating' fields and will usually raise issues of one kind or another. This is probably one of them. It mimics a spreadsheet, which is not conducive to databases. You should have something more along the lines of a field (e.g. ComType) with record values that are the PK values (usually autonumbers from tblCommitteeType) as the FK (foreign key) values for those records. If that all sounds like Greek to you, then you need to study db normalization before doing anything else. I can provide some links if you need them.

    Once the schema is fixed, getting anyone's list/count of committees will be simple. Not only that, if you need to edit a committee name, your approach is more difficult and liable to failure. Also, adding a committee type will upset a lot of design.
    Last edited by Micron; 11-22-2021 at 05:07 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    dylntbrown is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2021
    Posts
    2
    Thanks for the reply Micron! I should have mentioned that I am newer to databases, but very experienced in spreadsheets, so it sounds like I'm thinking its too much like a spreadsheet.

    Yes, this all does sound fairly foreign (I think?), if you wouldn't mind providing some links I sure would appreciate it!

    FWIW I have a table called "members" where I list all members in the overall club. then there are fields for last name, first name, email, committee1, committee2, etc. Each record also has a autonumber that is the Primary Key (PK?). Just not sure how to deal with the FK stuff...

    Thanks!

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am newer to databases
    Then you get the whole enchilada!
    Make sure you understand normalization, not just review it. If those links don't do it for you, find ones that do.
    Suggest you create relationships and post a pic of that, all the while incorporating good naming techniques and so on.

    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/

    Entity-Relationship Diagramming: Part I, II, III and IV
    http://rogersaccessblog.blogspot.com...ng-part-i.html

    How do I Create an Application in Microsoft Access?
    http://rogersaccessblog.blogspot.com...cation-in.html

    Important for success:
    Naming conventions - http://access.mvps.org/access/general/gen0012.htm
    https://www.access-programmers.co.uk...d.php?t=225837

    What not to use in names
    - http://allenbrowne.com/AppIssueBadWord.html

    About Auto Numbers
    - http://access.mvps.org/access/general/gen0025.htm

    The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
    Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
    About calculated table fields - http://allenbrowne.com/casu-14.html
    About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

    Quick & dirty example of how it might work with committees and members:


    tblCommittees
    CommIdPK Committee
    1 Welcoming
    2 Card Night
    3 Gifts
    4 Coffee

    tblMembers
    MemberIdPK Lname Fname Address etc.
    1 Brown Thomas
    2 Green Mary
    3 White Brian

    tblMemberComm
    MemberCommIdPK MemberFK CommitteeFK
    1 1 1
    2 1 2
    3 2 2
    4 2 4

    When you join the primary/child (1/many) tables in a query you find that Mary Green is a member of Card Night and Coffee committees. You get the actual text values from the parent table fields. The 3rd table is known as a junction table because the relationship is many to many.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Your requirement for matching members with committees needs a table structure of many-to-many, with a junction table joining the members table with the committee table (which apparently does not exist yet). The junction table would have to be limited to 2 rows per member for the current year.
    For an example of how to handle m-t-m see this:

    Many-To-Many, two ways to update Junction table, includes Not In List handling (accessforums.net)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-30-2019, 10:16 AM
  2. Replies: 2
    Last Post: 03-09-2018, 07:11 PM
  3. Managing Group Membership
    By MZYORK in forum Access
    Replies: 2
    Last Post: 11-09-2016, 12:04 PM
  4. Bar Code / Membership #
    By RustyRick in forum Access
    Replies: 1
    Last Post: 08-19-2016, 04:42 AM
  5. Club membership
    By Damo10 in forum Database Design
    Replies: 1
    Last Post: 09-16-2014, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums