Results 1 to 11 of 11
  1. #1
    Join Date
    May 2006
    Posts
    14

    Unanswered: Complete Newbie... relationships issue.

    can anyone suggest how to structure my relationships here?

    Building a contacts database in MS 2003.

    I have a main table, tblcontacts, with all the standard fields. One of the fields is for member type, (personal, honorary, corporate) which operates a lookup combobox, referencing a subtable, tblemembertype.

    There is a unique table for each member type. tblmemberpersonal, tblmemberhonorary, etc.

    No problems setting up the combobox to select a member type.

    What I cant figure out, is how to associate the correct unique member type tables, with the records in the main contacts table, when the particular member type is selected through the combobox.

    What I cant figure out, is when I select a member type (through the combobox) on an individual record on the main contacts table, how to associate the correct unique member type table.

    I hope Im making sense. Ive had great support on the forums. Thanks for putting up with me.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Uh ... 1 to Many from tblmembertype to your tblcontacts table ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2006
    Posts
    14
    Hey, could someone take a look at this if they get a chance? Its a screenshot of my relationships window. Its not right, but should show what Im getting at.

    Im trying to associate the 4 member types routed through tblmembertype, so that only the relevant member type is associated with the main tblcontactinfo when the member type is selected through combo box.

    Ive been working on this for weeks now and am crying with frustration Can anyone tell me if this is a workable approach? or should I be trying a different direction?

    Thanks
    Attached Thumbnails Attached Thumbnails Relationships.JPG  

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Why did you put each membership type in a seperate table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2006
    Posts
    14
    Hi Teddy, I assigned them their own tables cuz they each have different criteria. I also figured that when it came to the design of queries/reports later, it might make it more efficient. This is my first db.

  6. #6
    Join Date
    May 2006
    Posts
    14
    Am I looking at this the wrong way do u think? Any and all input appreciated.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I get the impression that you could probably streamline your tables a bit. What does tblMembershipStatus indicate? Is there a reason to have "Renewed From"? How about "Date Renewed"? I see Expiry and DaysToExpiry, that looks like a derived field to me...

    How about the Prospect stuff, how does that work logically speaking?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    May 2006
    Posts
    14
    Hi Teddy, Thanks for the reply. As far as the contents of the tables, believe me, I wish they were a bit simpler.

    tblMembershipStatus indicates whether a membership is active, expired or withdrawn.

    As far as the Renewed From date, its relevant where a membership is renewed concurrently from the date of natural expiry, as opposed to the actual date that the member was in contact and paid renewal fees.

    Date of Expiry needs to go in. And Days to Expiry, is really a derived field solely in place for a subsequent, user defined query.

    The Prospective Membership, is potentially my most complicated Member Type. I still havent thrashed out the finer details. But it is basically to keep track of dealings with people who are interested but have not taken out membership. It is also worth understanding whether members were categorised as prospective before they upgraded to full membership or withdrew interest.

    This db would be so so simple if it was one set of rules for every member type and I would be finished weeks ago. Im well on the way with it at the moment. Have my tbls constructed, forms and subforms operating, queries and reports are coming together too. And I like the way its shaping up. I just cant seem to relate the individual membership categories to the different contacts.

    Am I going about this completely upside down or inside out? Thanks for the input so far.

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Seems to me like you could have one set of dates for membeship status, effective date and termination date. You can derive days to expiration and renewal history through other means.

    Prospects would be best in an entirely different structure all together. They are NOT members, so don't put them in the members table. By assigning them a membership, you restrict what you can do with prospects.

    I would go something along this route:

    tblContact
    -------------------
    all the stuff in your tblContactInfo table goes here

    tblMemberShip
    --------------------
    contact_id
    membership_type_id
    eff_date
    term_date

    tblMembershipCategory
    ---------------
    membership_category_id
    description

    tblProspect
    --------------------
    prospect_id
    contact_id




    It feels like you're trying to cram too much business logic into the data layer. ONLY STORE DATA IN TABLES. You can do the business logic, statistical analysis and presentation stuff at the application level.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  10. #10
    Join Date
    May 2006
    Posts
    14
    Thanks for the advice Teddy, I think you are right about thinning down on the fields in the membership tables. Point taken, derived information through other means.

    However, while there are similarities between the unique member type tables, I feel that if I give them each their own table, I can allow for the db to be developed further in the future.

    I dont quite track what you were suggesting about Prospective membership, where do you see it fitting in? If I understand you, Your suggested setup, isn't that far from the approach I have taken, except, instead of 4 external unique member type tables, you have one for prospect. If so, I would probably run into the same problems Im having, eg. relating the prospective member table to the contact info when that member type is selected for a record.

    I have attached the latest version of my Relationships window. While it hasn't incorporated the changes you mentioned yet , I just need someone to tell me that yes, its a fairly standard looking setup, or no it looks like a salvador dali painting, and will never work. Ive had some results with the links but have yet to tweak them.


    Thanks for sticking with me on it.
    Attached Thumbnails Attached Thumbnails Relationships.JPG  

  11. #11
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That might work, kind of. You're severely limiting what you can do with that setup though.

    What happens if a prospective member joins on a personal level, then manages to get their corporation involved. how do you track how that developed?

    Now if you had a Membership table that simply had a membership type, when it was effective, and when it was terminated, it would be a very simple matter to pull all memberships, what type of membership it was, and when it was effective. You could also do wacky thing like allowing the same person to hold different concurrent membership types. This could be useful if a person owns a couple corporations.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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