Results 1 to 5 of 5

Joining two tables with two join criteria using a query, return wrong result

  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    38

    Question Joining two tables with two join criteria using a query, return wrong result

    Hello



    I'm having a bit of difficulty wrapping my head around some queries that I need joined correctly to each other, in order to get the correct result.

    Basically I got a database with a sewage network in it, the network has manholes with IDs and pipes with IDs. Each manhole and pipe has a unique ID. A pipe consists of an upstream and downstream manhole, each with separate IDs.
    Example: Manhole 1 (ID: M1) Manhole 2 (ID: M2) makes a Pipe (ID: P1) consisting of upstream ID M1 and downstream ID M2.

    The problem occurs when several pipes are in one manhole, which is defined as the upstream manhole for several of the pipes. Then it returns the correct manhole IDs but the wrong pipeID, as it is linked to the upstreamnode (where as I would like for it to check for both upstream and downstream in order to have the correct pipe ID)

    It's a bit hard to explain, but made a paint drawing the tries to illustrate what I mean and the result I get.

    Click image for larger version. 

Name:	QueryQuestion.png 
Views:	8 
Size:	19.2 KB 
ID:	25296Click image for larger version. 

Name:	Queryresult.png 
Views:	8 
Size:	42.9 KB 
ID:	25295

    Any hints to how I can correctly join this together would be much appreciated. I can't change the tablestructure, as it is linked to a program.
    Attached Thumbnails Attached Thumbnails Somethinglikethis.PNG  

  2. #2
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    38
    The above query gave an error, tried doing two different queries, and now I get half a result. It doesn't duplicate the pipeID, instead it just returns blank.
    Attached Thumbnails Attached Thumbnails Querytrialanderror.jpg  

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    11,819
    How do you describe your business? For example, Pipe 40 upstream has Manholes 1,14,35,64.... ?
    Are all Pipes identified as Upstream and Downstream?
    Can a manhole be associated with more than 1 pipe?
    Is it possible that a pipe can be both upstream and downstream?
    Do you have other identifiers for manholes (lat/Long)?

  4. #4
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    38
    Quote Originally Posted by orange View Post
    How do you describe your business? For example, Pipe 40 upstream has Manholes 1,14,35,64.... ?
    Are all Pipes identified as Upstream and Downstream?
    Can a manhole be associated with more than 1 pipe?
    Is it possible that a pipe can be both upstream and downstream?
    Do you have other identifiers for manholes (lat/Long)?
    Thanks for the reply

    All pipes are defined with one UpstreamManholeID (OpstroemKnudeID) and a DownstreamManholeID (NedstroemKnudeID) (Knude table = manhole). Each ID is unique, as is the Pipe ID (Ledning table)

    Click image for larger version. 

Name:	PipeTabelandManholeTabel.PNG 
Views:	7 
Size:	59.9 KB 
ID:	25299

    A manhole can be associated with several pipes, as the manhole may have several pipes entering it.
    Click image for larger version. 

Name:	pipewithsamedownstream.jpg 
Views:	7 
Size:	155.4 KB 
ID:	25300

    A manhole can be both upstream and downstream, see in the Ledning table ID 8 10 40, where the downstream is 9 in the first and downstream in the other two.

    Not that are usable I think, as everything needs to be linked to the IDs. But for some there is a coordinate set, but not everyone.

  5. #5
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    38
    Not sure if subqueries are possible in relation to my problem? Still hopelessly stuck on this.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Join fields in 2 tables and adding a criteria
    By elenaluiza in forum Access
    Replies: 2
    Last Post: 11-09-2015, 02:25 PM
  2. Joining multiple tables get wrong result
    By Tony Thi in forum Queries
    Replies: 3
    Last Post: 10-16-2015, 07:54 AM
  3. Replies: 12
    Last Post: 09-10-2015, 04:33 PM
  4. Multiple criteria query joining two tables
    By Carnior in forum Queries
    Replies: 15
    Last Post: 05-01-2015, 07:24 AM
  5. Access giving wrong result using sql query
    By amlanroy in forum Access
    Replies: 3
    Last Post: 05-10-2012, 09:11 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums