Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Relationship

  1. #1
    Join Date
    May 2003
    Location
    UK
    Posts
    233

    Unanswered: Relationship

    What is the value of creating relationships in the Relationship Tool of MS Access. The relationship can always be created in the queries SQL etc.

    So should I be worried by this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, you don't have to worry

    when you say "relationship can always be created in the queries SQL" this is not quite right, unless you know how to declare foreign keys with SQL (a very rare skill for Access developers)

    the value of creating the relationships with the relationship tool is that it's really easy to create relationships
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    You have me worried now!

    When would I use a foriegn key and not be able to create this in the queries or SQL.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh - I'll leave Rudy to clear that up for you.

    Nothing to worry about - you have confused JOINS and RELATIONSHIPS. Sadly this is very common. What you think of as a relationship in a SQL query is a JOIN.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    do you know what a foreign key is?

    you would use a foreign key to ensure relational integrity (you could google this phrase if you need to)

    thus, the FK is the relationship, and the Relationship Tool lets you build it easily

    as for queries, of course you can join two tables on whatever columns you want, that's a different issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And Referential Integrity is quite important imo, so creating relationships at the table level is wise, don't rely on joins in queries to maintain that referential integrity.
    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    declaring a relationship in the query designer is throwing away the advantage and power of a relational DB. you are sacrificing the data integrity that a well designed RDBMS can bring. Its ok, I suppose of the data is merely a port of data stored elsewhere.

    However if you don't validate the data integrity by relationships then you will either have to do a lot more programming to enforce the validity or just not bother and hope no fickle user destroys the data structure.

    the only grounds I can see for not using the relational part of a RDBMS are:
    either the db isn't normalised,
    it isn't a serious (read working DB),
    the developer doesn't know very much about Relational design.
    the developer doesn't care about the quality of the data
    the developer is a masochist and wants to do the hard work of enforcing relationships in code rather than in the DB.
    the developer doesn't understand relational theory and is embarassed to ask/persue.

    having said that you don't NEED a RDBMS, you can design out most of the problems by not using a RDBMS or ignoring the features of a RDBMS.. it 'just' requires more work.

    you can impose your own systems and controls to force integrity programmatically.. however its a heck of a lot harder, nowhere near as safe as using the RDBMS tools.

    there is actually another VERY VERY good reason to use RI constraints, especially if you use the query designer in Access... any relationship will pop up in the query designer SQL automatically on including the various tables
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    there is actually another VERY VERY good reason to use RI constraints, especially if you use the query designer in Access... any relationship will pop up in the query designer SQL automatically on including the various tables
    Although I go along with the "feature" to a degree, this is another thing on my list of Access Pet Hates. I think that declaring whether or not the query joins are INNER, LEFT OUTER or RIGHT OUTER (in effect even if the terminology is different) in the relationship designer is partly to blame for the confusion many Access-only users have about relationships and joins. Useful feature, dangerous for people that don't understand exactly what is going on.

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah but it's quite helpful when you know what you're doing... saves you having to manually make all your joins.
    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

  10. #10
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    One of the frustration of working on a database that has the relationships predefined is when creating a query the resulting information can be misleading. For example if you have contacts table and sex table. The contacts SexID is linked to that table.

    1. Male
    2. Female

    In a query if you select the Contact.SexID it would show up as Male or Female and not 1 or 2. Whne you put this in a report it shows as 1 and 2. Not Male and Female.

    Secondly, creating the joins in a query ensures that the person creating that query is fully aware, and has a greater understanding, of the relationship between the tables they are using.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by HelpMePlease
    In a query if you select the Contact.SexID it would show up as Male or Female and not 1 or 2. Whne you put this in a report it shows as 1 and 2. Not Male and Female.
    That sounds like you have lookups defined in the tables. Get rid of them - they can cause corruptions. This is another Access "feature".

    It is probably worth pointing out that relationships are absolutely integral to relational database design. Just about everything you describe is specific to Access, not databases.

  12. #12
    Join Date
    May 2003
    Location
    UK
    Posts
    233
    Look Ups, Look Ups!, Oh my how little I know.

    I thought this is a standard setup on MS Access, why is this so if it can lead to corrupt data, and how can these be turned off?

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Read the linked list, and also click the lookup fields link too:
    http://www.mvps.org/access/tencommandments.htm

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Read the linked list, and also click the lookup fields link too:
    http://www.mvps.org/access/tencommandments.htm
    good one Poots...

    I've always felt that page is absolutely required reading for all developers in Access. In fact I've thought it should almost be the first thing visitors to the Access thread here should see and read.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It thought it was more for fun than anything ^^

    I use GoTo a lot -- specifically GoTo CleanExit instead of Exit Sub so I break the "commandments" too often. I guess that makes me "dark side"
    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
  •