Results 1 to 5 of 5
  1. #1
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126

    Red face Relatioship,query or ???

    I have a membership app. It has two tables: Members and Sponsers.
    Any member can be a sponser and 90% of sponsers are members. But a member does not have to be a sponser. I have a working combo box of members and I would like to have a combo box of sponsers that populates from members; but 'not in list' allows for a non member tobe sponser. Should I use a self join on members tbl? Since a non member can sponser, is it best to delete all records in sponser and then append from members? Your ideas are appreciated. thanks.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why not just have 1 table. add a field MEMTYPE (text 5)
    that can have M, or S or MS

    then all are in 1 table, and you can filter Members, or Sponsors or Both.

  3. #3
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    or more easy for later queries: 3 tables with a many to many relationship. If later on you need to add a new type like Suppliers or Management you can just add it to the types list, or you can add a column to the linking table
    example:
    Click image for larger version. 

Name:	Contacts.jpg 
Views:	28 
Size:	35.3 KB 
ID:	46449

  4. #4
    Synergy.ron@gmail.com is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2020
    Location
    washington
    Posts
    126
    Thank you for the idea. But I also "I would like to have a combo box of sponsers that populates from members. since the app will be distributed with sample dataand that data will be deleted so that current, relevant data may be inserted" IT SEEMSLIKE I SHOULD USE A sponsers TABLE for the combo . But, how can I add to the sponsers table as members table get new members? Append query for all Member types="S"?
    Thanks



  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Best is keeping members and sponsors in 1 table or you will keep running into trouble. You can use ranmans solution and populate the combo with a query expression that gets all members where type = S or type = MS. Or you can take the solution with 3 tables and get all members that have in table tblContacttypes an entry of type Sponsor. In this case you'll have a query expression with 2 tables, but still very simple to do.
    Any way you choose, you're data will be up to date without any extra actions.

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

Tags for this Thread

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