Results 1 to 6 of 6

Thread: Relationships

  1. #1
    Join Date
    Nov 2009
    Posts
    3

    Unanswered: Relationships

    I need help cleaning up my relationships for queries and reporting. When I try to run a query with information on more than two tables it won't show anything. I know this is because my relationships are messy.

    This is because all tables share information. The database is used to run a script writing peer review system. Writers read other writers scripts and give feedback. We log a request for a script and the script feedback. The problem I have is that the writers are also readers.

    Please take a look at my relationships and let me know how I could make access know what I need. Any help would be very useful.
    Attached Thumbnails Attached Thumbnails relation.jpg  

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Welcome to the forums

    First off, I would recommend a better naming convention in order to avoid using a problem word, it'll save you headaches in the long run.

    Second, I don't know what your experience level is, but I'd make sure you have a firm grasp of the relational database design before laying out your table structure. Again, will save a lot of headaches down the road for you.

    HTH!
    Me.Geek = True

  3. #3
    Join Date
    Nov 2009
    Posts
    3
    Thanks for that. I will read that article. I need the database to be able to merge from all tables at once which is difficult as there are many relationships. I can't work out when to do a lookup or a relationship.

    I am a beginner.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    NEVER use Lookups in tables. Ever!

    Feedback, Requests, Scripts and Writers.

    Feedbacks.
    How many requests does a feedback have? None, One or many?
    How many scripts does a feedback have? None, One or many?
    How many writers does a feedback have? None, One or many?

    Requests.
    How many feedbacks does a request have? None, One or many?
    How many scripts does a request have? None, One or many?
    How many writers does a request have? None, One or many?

    Scripts.
    How many feedbacks does a script have? None, One or many?
    How many requests does a script have? None, One or many?
    How many writers does a script have? None, One or many?

    Writers.
    How many feedbacks does a writer have? None, One or many?
    How many requests does a writer have? None, One or many?
    How many scrpts does a writer have? None, One or many?

    ALL of these questions need to be answered accurately before anyone can help you design your table relationships.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Nov 2009
    Posts
    3
    Thanks for this. Sorry for my late reply.

    Feedbacks.
    How many requests does a feedback have? None
    How many scripts does a feedback have? One
    How many writers does a feedback have? One

    Requests.
    How many feedbacks does a request have? None
    How many scripts does a request have? One
    How many writers does a request have? One

    Scripts.
    How many feedbacks does a script have? many?
    How many requests does a script have? many?
    How many writers does a script have? One

    Writers.
    How many feedbacks does a writer have? many
    How many requests does a writer have? many
    How many scrpts does a writer have? many

    Following this method gives the relationship structure that I have posted here. I think the problem is that writers are also readers (as this is a peer review system). It's the one to many writer - script relationship that causes the problem as they have a many to many relationship through requests and feedbacks.

    I can't work out how to get around this.

    *Edit* My lookups are only for reference. I hide the foreign key by giving it the display width of 0cm and add other name fields so I can see what I am selecting when creating a new record. The foreign key is always the bound field.
    Last edited by motherlover; 11-10-09 at 18:23.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so then your relationships seem ok. Which leads to the problem you are having that makes you suspect your relationships are wrong.

    Perhaps you just need to delete a link in your query to allow data to come through.

    The Script - Writers relationship is most likely the one to remove, but I can't be sure as I don't know what you're doing.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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