SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating table based on Yes/No field in Access

    I am currently working on a basic membership database in Microsoft Access where a member can be a variety of different types (Recruit, Player, Parent, Alumni). There is one table called MEMBERS that stores basic personal information as well as fields for each member type. Each field/column pertaining to Membership Type has a Yes/No Datatype.

    This MEMBERS table is related to various membership type tables such as RECRUIT, PLAYER, PARENT, ALUMNI based on Member_ID.

    My question is this:

    How can I have the Member_ID show up on the associated membership type table once the membership type field is checked in the MEMBER table.

    Thank You!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by NapoleonB View Post
    How can I have the Member_ID show up on the associated membership type table once the membership type field is checked in the MEMBER table.
    you need to insert the row with that member_id value into the appropriate membership table

    the four yes/no fields aren't actually required, except if you are retrieving information only from the main members table without any data from the associated membership type tables

    if you want to show data from both, you'll have to do 4 LEFT OUTER JOINs, and then you won't actually need the Yes/No fields
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you need to insert the row with that member_id value into the appropriate membership table

    the four yes/no fields aren't actually required, except if you are retrieving information only from the main members table without any data from the associated membership type tables

    if you want to show data from both, you'll have to do 4 LEFT OUTER JOINs, and then you won't actually need the Yes/No fields
    So that would involve vb coding?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    um, no, except insofar as you need some language or another to run your queries, assuming you're not always going to be feeding them in via the command line

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Mar 2009
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just want to clarify some things as I'm new to databases and Access:

    On the form that i create, say there's a section for identifying member type for each record/member. There are about 6 membertypes and when one is checked, fields pertaining to that membertype become visible.


    Would i just need to write code that has the member_id go from MEMBER table to a membershiptype table such as PARENT or PLAYER when the box is checked on the form? With the subsequent membershiptype info going to that table as well.

    Thanks.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, that's the general idea

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •