Thanks Thanks:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Acccess Query 0 Results with Relational Table

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

    Default Acccess Query 0 Results with Relational Table

    I have a query that works fine. As soon as I add a table that has a relationship to the main table I start getting 0 results.

    I believe the issue is that I am trying to have 3 fields as foreign keys that link back to the same primary key in another table.


    For instance i have tblRaters that has information on everyone in a rating scheme.
    For the rating scheme we have raters, senior raters, and reviewers. These people could all be the same person in different roles for different people.

    In my tblprsnl I have a raterID, srRaterID, and reviewerID, which all link back to tblraters.raterID.

    When I only have one relationship made it works. as soon as I add anymore to the tlbraters.raterID it fails.

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,398
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    If your query is returning 0 records, it usually indicates one of two things:
    - you have a problem with your relationship, or how you are joining all the tables
    - there are no records that match on all three tables

    What might be helpful is to post small data samples of each of your three tables.
    Then post the query that you have that works (switch to SQL View and post the SQL code here).
    And post the SQL code of the query that you attempted that does NOT work.
    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. #3
    Board Regular
    Join Date
    Sep 2008
    Posts
    276
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    not really sure how to add part of the database but ill try.

    tblPrsnl
    prsnlID
    firstName
    lastName
    raterID
    srRaterID
    reviewerID

    tblRaters
    raterID
    raterFirstName
    raterLastName
    raterEmail

    In tblPrsnl raterID, srRaterID, and reviewerID all have a relationship with tblRaters.raterID.

    Code:
    SELECT tblRank.rankID, [Last Name] & ", " & [First Name] & " " & [Middle Initial] AS ncoName, [tblRank] & "/" & [Job Title] AS ncoRankTitle, "Date Assigned Duty: " & Format([Date assigned],"yyyymmdd") AS ncoDateAssignedDuty, "DOR: " & Format([DOR],"yyyymmdd") AS ncoDOR, "Last NCOER: " & Format([LastNCOER],"yyyymmdd") AS ncoLastNCOER, "AKO: " & [ako email] AS ncoEE, Prsnl.isDeprecated, Prsnl.Organization, tblRank.tblRank, Prsnl.raterID, tblRaters.raterLastName, tblRaters.raterFirstName, tblRaters.raterLastName, tblRaters.raterFirstName
    FROM (tblRank INNER JOIN Prsnl ON tblRank.rankID = Prsnl.Rank) INNER JOIN tblRaters ON (Prsnl.srRaterID = tblRaters.raterID) AND (Prsnl.raterID = tblRaters.raterID)
    WHERE (((Prsnl.isDeprecated)=0) AND ((Prsnl.Organization)="CEHC") AND ((Prsnl.employmentType)="NCO" Or (Prsnl.employmentType)="Officer"))
    ORDER BY tblRank.rankID, [Last Name] & ", " & [First Name] & " " & [Middle Initial];
    My thoughts are to give the tblRaters 2 new unique identifer columns and use those to make relationships instead?

  4. #4
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,398
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    No, I want to see the actual data, so I can see what the values are in the fields that you are trying to join on.

    If you limit your query to just a few records, you can use Copy and Paste to select the results of the query and paste here, like this:
    Reg Status CountOfStatus
    1 Issue 3
    3 Issue 1
    5 Issue 1
    7 Issue 1

    Also, please post the SQL code for the query that you say is working properly (the one that only has two tables).
    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!"

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

    Default Re: Acccess Query 0 Results with Relational Table

    This is the query that works.
    Code:
    SELECT tblRank.rankID, [Last Name] & ", " & [First Name] & " " & [Middle Initial] AS ncoName, [tblRank] & "/" & [Job Title] AS ncoRankTitle, "Date Assigned Duty: " & Format([Date assigned],"yyyymmdd") AS ncoDateAssignedDuty, "DOR: " & Format([DOR],"yyyymmdd") AS ncoDOR, "Last NCOER: " & Format([LastNCOER],"yyyymmdd") AS ncoLastNCOER, "AKO: " & [ako email] AS ncoEE, Prsnl.isDeprecated, Prsnl.Organization, tblRank.tblRank, Prsnl.raterID, tblRaters.raterLastName, tblRaters.raterFirstName
    FROM (tblRank INNER JOIN Prsnl ON tblRank.rankID = Prsnl.Rank) INNER JOIN tblRaters ON Prsnl.raterID = tblRaters.raterID
    WHERE (((Prsnl.isDeprecated)=0) AND ((Prsnl.Organization)="CEHC") AND ((Prsnl.employmentType)="NCO" Or (Prsnl.employmentType)="Officer"))
    ORDER BY tblRank.rankID, [Last Name] & ", " & [First Name] & " " & [Middle Initial];
    Rank raterID raterLastName raterFirstName
    SFC 1 Crane Ichobod

    raterID raterLastName raterFirstName raterMI raterRank raterPosition raterEmail
    1 Crane Ichobod D Supv USD
    2 Fett Bobba
    Unit SPT NCO
    3 Jobs Steve E Tech Director
    4 Beck Robert R Inst/Writer
    5 Presley Elvis T Director
    6 McPhee Nanny W Tng Spec
    7 Gates Bill J Supv Intel
    8 Martin Steve D Instr (K9)
    9 Patrick Patrick D CG
    10 Ramirez Robert E Deputy


    raterID srRaterID reviewerID
    1 3 10

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

    Default Re: Acccess Query 0 Results with Relational Table

    Odd. When i pasted it had the table name at the top.

    Anyway. The first is the query, the 2nd is tblRaters, and the 3rd is tblPrsn.

  7. #7
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    47,398
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Acccess Query 0 Results with Relational Table

    You aren't showing the rank or rankID fields in your data examples.
    I need to see the values of the fields that you are joining on (and you are joining on those fields).
    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!"

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

    Default Re: Acccess Query 0 Results with Relational Table

    A good way to debug this would be to take one record that you think should be in the query results, and analyze how it fits the join conditions in the query to see why it is being excluded (which is exactly what Joe or I would do if we could see an example with all the relevant data).

    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

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

    Default Re: Acccess Query 0 Results with Relational Table

    I didnt put the other things because they are not the issue. The issue is specifically when i add a relationship from 3 fields in the tblRater to the 1 raterID field in the tblPrsnl table. When it is only one relationship it works fine, as soon as I add a 2nd relationship it stops working.

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

    Default Re: Acccess Query 0 Results with Relational Table

    The issue is specifically when i add a relationship from 3 fields in the tblRater to the 1 raterID field in the tblPrsnl table.
    Hmm - curious. What do you mean by joining three fields to one field? I'm not sure what that means - what three fields?

    In general, the suggestion in my previous post will still be a good way to debug this - take one row that you think should be in the result and see how it meets the join criteria for the table joins.

    For more help on the forum, we would need to see the SQL for the query you are creating (even if it doesn't work), and ideally some sample data (but just enough to be able to work on a sample, not hundreds of rows of sample data as that's just overkill).

    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

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
  •