Results 1 to 7 of 7
  1. #1
    robertbarker is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Massachusetts
    Posts
    4

    Many to Many Database design question

    I have a small database to help me track a series of concerts we are hosting at our home and I am not sure if I have the structure correct here. I have a table called "Attendees" that is a master list of all the people who could potentially be invited, a table called "Event" that contains the date, performer, and the people who actually were invited and are attending. Since there could be multiple people attending multiple concerts I set up a many to many relationship using the table "EventAttendee". Here is where it gets fuzzy for me, I want to be able to pick the attendees for each event off of the master list so I set up a field in Event called "Invited" and made it a lookup link to "AttendeeID" in the Attendee table and shoved in a combo box. Now I'm second guessing myself and getting incorrect data in my query and form attempts. I'm thinking now that I might need to delete the look up because the many to many table will record the data. Is this correct? I have attached a copy of the data base with no data in it for reference.

    I know this is a silly little project but it's about the fundamentals for me since I am working on a much larger project at work where I am struggling with the same concepts. I have some experience over the years with database technology beginning with DBase III and then Access 97 up through current but I never tried to design anything using many to many before. I had a coworker who was working on this project with me but she resigned and I freely admit I'm over my head so I figured if I designed this little concert data base and got it working I could apply those concepts to the larger project.

    Thanks for any help you can provide me with. Concerts No Data.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you use multi-value field in Event, there is no need for EventAttendee table.

    Most experienced developers advise not to use multi-value fields nor to build lookup fields in table.

    Use query that joins tables to view related info.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    MTM Schedule Events-davegri-v01.zip

    Maybe this approach would work for you. It uses Members instead of Attendees, which is only a name that could be changed.
    It uses side-by-side list boxes to manage the M-T-M assignment of members to events.

    Click image for larger version. 

Name:	MTM.png 
Views:	24 
Size:	33.5 KB 
ID:	41008

  4. #4
    robertbarker is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Massachusetts
    Posts
    4
    Quote Originally Posted by June7 View Post
    If you use multi-value field in Event, there is no need for EventAttendee table.

    Most experienced developers advise not to use multi-value fields nor to build lookup fields in table.

    Use query that joins tables to view related info.
    Thanks for your quick response! It seems like you are suggesting that I remove the "EventAttedee" table and use the multi-value field in the "Events" table to track people. But then, in reading the rest of your reply you say that it is not the recommended way to approach this. I understand your point about using a query instead but wouldn't I still need a field that will accept multiple values since more than one person will be attending? Or is it just the fact that I've linked all the name and email data to the record in the table.

  5. #5
    robertbarker is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Massachusetts
    Posts
    4
    Thank you very much Davegri, I'll have a look at it when I get a chance and let you know if it works out.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    No, I was not suggesting you use multi-value field. I merely said if you choose to do so there is no need for EventAttendee table because this duplicates data. The dependent table structure is recommended. Each event/attendee pair will have a record in EventAttendee. Why do you think a multi-value field is needed when EventAttendee table shows who went to event?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    robertbarker is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Location
    Massachusetts
    Posts
    4
    Quote Originally Posted by June7 View Post
    No, I was not suggesting you use multi-value field. I merely said if you choose to do so there is no need for EventAttendee table because this duplicates data. The dependent table structure is recommended. Each event/attendee pair will have a record in EventAttendee. Why do you think a multi-value field is needed when EventAttendee table shows who went to event?
    Thank you for your patience and I apologize for the confusion. I think I've got it now, don't need both, dependent table structure recommended over multi-value field, use query to combine the data. I guess my confusion was (is) based on a poor understanding of how the dependent table structure works. This made it difficult for me to see how to get the data out in a meaningful way. I saw plenty of resources that told me how to set up the table but they stopped short of explaining how to set up the queries get your results.

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

Similar Threads

  1. Possible question in the design of a database
    By AzizSader in forum Forms
    Replies: 3
    Last Post: 04-21-2014, 11:17 PM
  2. Database design question
    By D347HxD in forum Database Design
    Replies: 3
    Last Post: 10-17-2013, 12:02 PM
  3. Database design question
    By audmkamp in forum Database Design
    Replies: 2
    Last Post: 01-21-2013, 01:48 PM
  4. Database design question
    By udigold1 in forum Database Design
    Replies: 3
    Last Post: 03-23-2012, 02:20 PM
  5. Database Design Question
    By AccessNewBiegr in forum Access
    Replies: 2
    Last Post: 02-08-2011, 08:22 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