-
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.
-
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.
-
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:
-
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
-
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.
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
-
Forum Rules