Power Query Course in Spanish
Thanks Thanks:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 31

Thread: Acccess Query 0 Results with Relational Table

  1. #21
    Board Regular
    Join Date
    Sep 2008
    Posts
    272
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    I tried those. It works as long as only one relationship is active. Work's great. Its as soon as I add the 2nd relationship to the primary key that it fails. If I could load the emptied out database I would.

  2. #22
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    46,653
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    Can you empty out the real data, fill it with some dummy data, and load to a file uploading site where we could download it?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #23
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    Just to be clear, adding a relationship (as long as you don't get a violation that actually prevents the relationship from being created) is not something that really can fail. If it is failing, I would need to see the error message. Relationships would only create problems when you are inserting or updating data that violate the relationship constraints.

    More likely, the problem must lie in the query you wrote.
    Last edited by xenou; Sep 15th, 2017 at 04:49 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #24
    Board Regular
    Join Date
    Sep 2008
    Posts
    272
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    For instance this works fine and gives me one result
    Code:
    SELECT Prsnl.[Last Name], Prsnl.[First Name]
    FROM tblRaters INNER JOIN Prsnl ON tblRaters.raterID = Prsnl.raterID;
    This is my result.
    Last Name First Name
    Adams Tory

    When I make one more relationship I get the following SQL.
    Code:
    SELECT Prsnl.[Last Name], Prsnl.[First Name]
    FROM tblRaters INNER JOIN Prsnl ON (tblRaters.raterID = Prsnl.srRaterID) AND (tblRaters.raterID = Prsnl.raterID);
    This automatically gives me:

    Last Name First Name


    That is why I was saying the relationship errors it out. There is not an actual error.

  5. #25
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,984
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    Hi,
    so this seems pretty straightforward to me.

    after you add this constraint:
    Code:
    AND (tblRaters.raterID = Prsnl.raterID)
    then you no longer have any records where both of these conditions hold true:
    • tblRaters.RaterID is equal to Prsnl.RaterID
    • tblRaters.raterID is equal Prsnl.srRaterID

    When I say both conditions hold true, I mean both conditions hold true at the same time.


    from a purely SQL point of view the result is perfectly valid and is simply that there are no matching results. Put another way (given the actual query you wrote in the last post), Tory Adams is the only person who has a RaterID in the Raters table, and Tory does not have a RaterID that is also the same as their srRaterID).

    So, as I have said, if you think this is not true, show the data where you think you should be getting a result (we need to see the records from both tables with the relevant RaterIDs and srRaterIDs).

    But given that you have no results, you probably need to also stand back a minute and think about what it is you are trying to query for - probably in plain English first, before you write (or edit) any SQL. Because as it stands you either want to get something from the data that isn't there, or you have written bad SQL that doesn't actually match what you want.

    A minor (possibly major) problem is this table structure is a bit awkward also. Having the three fields for three different "IDs" which actually all relate to one ID field in another table is more or less asking for trouble. I'm not really sure though - it just looks fishy to me.
    Last edited by xenou; Sep 16th, 2017 at 02:16 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #26
    Board Regular
    Join Date
    Sep 2008
    Posts
    272
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    Sorry I missed where you asked for me to upload to a site. Ill do that tomorrow and upload it, while working on the last comment.

  7. #27
    Board Regular
    Join Date
    Sep 2008
    Posts
    272
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    I rebuilt the tables in SQL and this code works
    Code:
    SELECT prsnl.id, 
           prsnl.lastname, 
           prsnl.firstname, 
           prsnl.rank, 
           prsnl.email, 
           prsnl.raterid, 
           rater.raterlastname, 
           rater.raterfirstname, 
           rater.ratermi, 
           rater.rateremail, 
           prsnl.srraterid, 
           srRater.raterlastname   AS srRaterLastName, 
           srRater.raterfirstname  AS srRaterFirstName, 
           srRater.ratermi         AS srRaterMI, 
           srRater.rateremail      AS srRaterEmail, 
           prsnl.reviewerid, 
           reviewer.raterlastname  AS reviewerLastName, 
           reviewer.raterfirstname AS reviewerFirstName, 
           reviewer.ratermi        AS reviewerMI, 
           reviewer.rateremail     AS reviewerEmail 
    FROM   tbleprsnl prsnl 
           JOIN tblraters rater 
             ON prsnl.raterid = rater.raterid 
           JOIN tblraters srRater 
             ON prsnl.srraterid = srRater.raterid 
           JOIN tblraters reviewer 
             ON prsnl.reviewerid = reviewer.raterid 
    ORDER  BY prsnl.lastname, 
              prsnl.firstname  
    Access SQL is different though

  8. #28
    Board Regular
    Join Date
    Sep 2008
    Posts
    272
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    https://www.dropbox.com/s/iy71toaqcw...ple.accdb?dl=0

    See if this works. Its had things deleted out of it and refilled in with an example. I didnt fully load everything back in the query because in reality if I can get it it work with the rater and senior rater it will be easy to add in the reviewer as well.

  9. #29
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,418
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    Access gets funny about parentheses, I think you just want:


    SELECT prsnl.id,

           prsnl.lastname,

           prsnl.firstname,

           prsnl.rank,

           prsnl.email,

           prsnl.raterid,

           rater.raterlastname,

           rater.raterfirstname,

           rater.ratermi,

           rater.rateremail,

           prsnl.srraterid,

           srRater.raterlastname   AS srRaterLastName,

           srRater.raterfirstname  AS srRaterFirstName,

           srRater.ratermi         AS srRaterMI,

           srRater.rateremail      AS srRaterEmail,

           prsnl.reviewerid,

           reviewer.raterlastname  AS reviewerLastName,

           reviewer.raterfirstname AS reviewerFirstName,

           reviewer.ratermi        AS reviewerMI,

           reviewer.rateremail     AS reviewerEmail

    FROM   (((prsnl prsnl

              LEFT JOIN tblraters rater

                     ON prsnl.raterid = rater.raterid)

             LEFT JOIN tblraters srRater

                    ON prsnl.srraterid = srRater.raterid)

            LEFT JOIN tblraters reviewer

                   ON prsnl.reviewerid = reviewer.raterid)

    ORDER  BY prsnl.lastname,

              prsnl.firstname 

  10. #30
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,418
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    or using your sample database:
    Code:
    SELECT prsnl.id, 
           prsnl.[last name], 
           prsnl.[first name], 
           prsnl.rank, 
           prsnl.enterpriseemail, 
           prsnl.raterid, 
           rater.raterlastname, 
           rater.raterfirstname, 
           rater.ratermi, 
           rater.rateremail, 
           prsnl.srraterid, 
           srRater.raterlastname   AS srRaterLastName, 
           srRater.raterfirstname  AS srRaterFirstName, 
           srRater.ratermi         AS srRaterMI, 
           srRater.rateremail      AS srRaterEmail, 
           prsnl.reviewerid, 
           reviewer.raterlastname  AS reviewerLastName, 
           reviewer.raterfirstname AS reviewerFirstName, 
           reviewer.ratermi        AS reviewerMI, 
           reviewer.rateremail     AS reviewerEmail 
    FROM   (((prsnl prsnl 
           LEFT JOIN tblraters rater 
             ON prsnl.raterid = rater.raterid)
           LEFT JOIN tblraters srRater 
             ON prsnl.srraterid = srRater.raterid)
           LEFT JOIN tblraters reviewer 
             ON prsnl.reviewerid = reviewer.raterid) 
    ORDER  BY prsnl.[last name], 
              prsnl.[first name]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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