Thanks Thanks:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: Acccess Query 0 Results with Relational Table

  1. #11
    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 understand. There is a lot of sensitive stuff in my database, so its hard to show it all with out having time to go back through and put more fake stuff in.

    When i say i joined 3 to one. I opened relationships and under tblRaters I dragged raterID over to tblPrsnl.RaterID, then I took tblRaters.srRaterID over to to tblPrsnl.RaterID, and then I did tblRaters.reviewerID to tblPrsnl.RaterID. Is there a way to submit a screenshot of the relationships?

  2. #12
    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

    Is the issue that it is a many-to-many and I should probably create a rating scheme table?

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

    Default Re: Acccess Query 0 Results with Relational Table

    Personally, I seldom set up relationships in my database. I just do all my joins directly in my queries, and leave it at that. Then all that matters is the SQL code of the query.
    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!"

  4. #14
    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

    Oh ok, didnt know that was an option in Access. I knew there was the SQL. So in this case I would just do multiple left Joins in SQL and be good.

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

    Default Re: Acccess Query 0 Results with Relational Table

    So in this case I would just do multiple left Joins in SQL and be good.
    Provided that you are joining on the correct fields, yes.
    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!"

  6. #16
    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

    Thanks,

    Ill give that a try and see how it goes.

  7. #17
    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

    Just to add into what I am talking about with the 3 foreign keys to the 1 primary key. nevermind it dont paste properly and I dont know how to delete.
    Last edited by geospatial; Sep 15th, 2017 at 01:41 PM.

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

    Default Re: Acccess Query 0 Results with Relational Table

    Okay. Are you talking about being unable to delete the relationships themselves?

    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. #19
    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

    oh no sorry, i meant the post. I had pasted my relationship diagram, but it didnt actually show and I cant delete that post.

    I am back to being lost here. Access SQL is not the same on writing it out as regular SQL is.

    I saw another post on another forum that basically says I need to do:
    You need to refer to the ProductLocations table twice, and each instance needs to join to either the ProductLocationLeaveID field or the ProductLocationEnterID field. Otherwise, you're looking for a location ID that matches the enter and leave IDs at the same time. Short of recording a movement from the bar to the bar (for example), you're not going to find any.

    That was in reference to another database someone asked, but it sort of made sense on what I needed to do to mine, just no idea how to do it.

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

    Default Re: Acccess Query 0 Results with Relational Table

    I still think you should do what I said earlier (twice):

    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).
    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.
    To have something concrete to look at is the only way I can think of to help you. Do you have a reason not to do this? You should be able to create dummy data if you don't want to post confidential data. It really does me very little good to hear about how some other query works in some other database. We need to work with your query in your database.

    By the way, you should also probably not discount the notion that a valid result for you query is that there are no results. So in order to test properly you must make sure there is at least one record that you know is supposed to be in the results.
    Last edited by xenou; Sep 15th, 2017 at 03: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

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
  •