Results 1 to 3 of 3
  1. #1
    nickh is offline Novice
    Windows 8 Access 2013 32bit
    Join Date
    Sep 2016
    Posts
    3

    Best way to organize a table to store data that can have multiple values at the same time

    I'm putting together a membership database for an organization I am part of. When members join the organization, we typically ask them what interests they have so we can schedule programs and outings appropriately. The form they fill out typically is setup as a checkbox list with activities such as skiing, baseball, basketball, football, soccer, music, etc. We then end the list with an blank line for them to suggest other activities. I'd like to store this data is a table within the database so we can run reports on the interests of the people who always attend our event versus the interests of people who sometime attend our events. The trouble is I'm not sure the best way to construct the table.

    One way I thought to construct the table is with a series of "Yes/No" fields for each interest. This corresponds well with the way the members are providing the data and easily keeps track of someone who is interested in more than one activity. However, this seems like it might get messy over time with all the activities really stored as fields. I'm also a little concerned about creating reports as the actual data I want are really the field names and not the field values. Another way I thought to construct the table is to have simply one field that can store multiple values in a combo box. However, I've had trouble in the past with These types of fields. First of all, I've found that queries of multi-select combo box data can count entries multiple times. I also remember Access restricting some of the export functionality when fields are allowed to have multiple values.

    Does anyone have any recommendations for the best way to store this type?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    construct the table is with a series of "Yes/No" fields for each interest
    Quite simply, no. Additions or deletions of activities (or any 'thing') that requires adding or no longer using a table field is the first indication that the db is not properly normalized.

    At a minimum, you should have tblMembers, tblActivity (with all activities listed in one field) and then to make the 'connection' between a member and their chosen activities, a junction table (tblMemberAct). The junction table would have a field for the member id (tblMembers.MemberID as a foreign key in tblMemberAct and tblActivities.ActivityID as another foreign key in tblMemberAct. If John (id = 32) chose 5 activities, there would be 5 entries for ID 32 in tblMemberAct, something like

    MembrID_FK ActID_FK
    32 2
    32 5
    32 8
    32 12
    32 15

    If you want to track meeting attendance, that is another thing, requiring at least a table for meetings and their dates. There are options when designing a form for this besides check boxes, which I think would be a mistake. Yes you could use a multi-select combo to choose the activities, but I think a multi select listbox is a superior choice. Don't bother creating a form until you have the proper table setup and queries that retrieve data correctly or allow data input.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron -- get your tables and relationships designed and tested to meet your requirements before getting too deep into Access.

    People--->PeopleIsInterestedInActivity<---Activity

    PeopleIsInterestedInActivity is called a junction table or linking table

    There is a record in the junction table for Each Person'sActivity interest, just as Micron said.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-28-2015, 12:09 AM
  2. Replies: 5
    Last Post: 11-17-2013, 04:20 PM
  3. Query to organize nested values
    By Seth Mannheim in forum Queries
    Replies: 11
    Last Post: 05-03-2013, 10:49 AM
  4. Replies: 12
    Last Post: 12-01-2011, 10:28 AM
  5. How do I get dlookup values to store in table
    By rpmyhero in forum Access
    Replies: 1
    Last Post: 11-25-2009, 05:57 AM

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