Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Help with a Relationship...

    For purposes of my project, a sewer line is defined by (1) the manhole it starts from, and (2) the manhole it ends at. I would like to find a valid way of creating a relationship in Access.

    For example, SewerLine #1 starts at Manhole #1 and ends at Manhole #2. SewerLine #2 starts at Manhole #2 and ends at Manhole #3.

    ---So I made two tables: SewerLines & Manholes.
    ---The SewerLines' primary key is a combination of 2 fields ("From_Manhole_ID" & "To_Manhole_ID").
    ---Both of these fields represent a single primary field of the Manhole table (Manhole_ID).

    Access isn't letting me create a relationship from the Manhole's primary key (Manhole_ID) to BOTH primary fields of the SewerLines table. From what little database experience I have, tables with two fields serving as a primary key are usually linking tables... is there an alternative way to create such a relationship in Access that actually works? Any info would be really appreciated.
    Attached Thumbnails Attached Thumbnails relationships.jpg  

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Help with a Relationship...

    Originally posted by thebign
    For purposes of my project, a sewer line is defined by (1) the manhole it starts from, and (2) the manhole it ends at. I would like to find a valid way of creating a relationship in Access.

    For example, SewerLine #1 starts at Manhole #1 and ends at Manhole #2. SewerLine #2 starts at Manhole #2 and ends at Manhole #3.

    ---So I made two tables: SewerLines & Manholes.
    ---The SewerLines' primary key is a combination of 2 fields ("From_Manhole_ID" & "To_Manhole_ID").
    ---Both of these fields represent a single primary field of the Manhole table (Manhole_ID).

    Access isn't letting me create a relationship from the Manhole's primary key (Manhole_ID) to BOTH primary fields of the SewerLines table. From what little database experience I have, tables with two fields serving as a primary key are usually linking tables... is there an alternative way to create such a relationship in Access that actually works? Any info would be really appreciated.
    Access will let you create both relationships. Using your example I just set it up. After you create the first one, Access will prompt you to edit the existing relationship or create a new one. It will then create the new relationship but put another copy of the table, representing the one side of the relationship, into the relationships window.

    Make sure the data types are the same in each of the ManholeID fields in both tables.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Feb 2004
    Posts
    3
    Thanks for your help.

    You're right, I was able to set up the relationship with the second copy of the Manhole table.

    However, I'm not sure if it's set up correctly since when I try to run a select query for all sewer lines that are related to Manhole #2 (please see attached image), nothing shows up in the query result (I wanted to display all sewer lines that either start or end with Manhole #2).

    Is there a better way to design a database that represents the features I described, or do I just need to alter the existing relationship? Thanks again...
    Attached Thumbnails Attached Thumbnails relationships2.jpg  

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by thebign
    Thanks for your help.

    You're right, I was able to set up the relationship with the second copy of the Manhole table.

    However, I'm not sure if it's set up correctly since when I try to run a select query for all sewer lines that are related to Manhole #2 (please see attached image), nothing shows up in the query result (I wanted to display all sewer lines that either start or end with Manhole #2).

    Is there a better way to design a database that represents the features I described, or do I just need to alter the existing relationship? Thanks again...
    Depending on what results you want to achieve, there are certainly other ways to set things up. What you have done can work. You could include another copy of the Manhole table in the query and link each one of them just as you did in the relationships window. Put the same criterea in the TManhole column and the FManhole column but create an "Or" condition. The problem with this is that if you want to pull data from the Manhole table you will pull more information than you want because for each FManhole that = 2 the TManhole value will be something different and vice versa. Try it and see for yourself.

    I would suggest that you try a Union query to bring the data together. You will have to create this in the SQL view of the query window. It can't be displayed in the design view. What you are basically doing with a union query is combining the results of two queries. Here's the example with your data.

    SELECT Sewerlines.Sewerline_ID, Sewerlines.Material, Manhole.Elevation, Manhole.Depth FROM Manhole INNER JOIN Sewerlines ON Manhole.Manhole_ID = Sewerlines.TManhole
    WHERE Manhole.Manhole_ID=2 UNION SELECT Sewerlines.Sewerline_ID, Sewerlines.Material, Manhole.Elevation, Manhole.Depth FROM Manhole INNER JOIN Sewerlines ON Manhole.Manhole_ID = Sewerlines.FManhole WHERE Manhole.Manhole_ID=2;

    Try this and see if it makes sense to you. Check the spellings of things if you try it. I tried to match what you were using but included some info from the Manhole table to illustrate.

    Come Monday, you'll probably have more help. Just thought I'd try to help keep you going for the weekend.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Feb 2004
    Posts
    3
    Thank you. I did what you suggested and all the queries run correctly. Although I'm not too experienced in SQL, I did understand what the Union Select accomplished.

    However, the relationships are still giving me trouble (please see the attached image). The problem is that when I try to view the related SewerLines from the Manhole table, nothing is displayed. Shouldn't the Manhole table display all the Sewerlines that use Manhole #2?

    Anyway, you've been such a big help already, I don't mean to take any more of your time. Thanks again.
    Attached Thumbnails Attached Thumbnails relationships4.jpg  

Posting Permissions

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