Acccess Query 0 Results with Relational Table

[geospatial]

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.

 

[geospatial]

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?

 

[geospatial]

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

 

[Joe4]

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.

 

[geospatial]

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.

 

[Joe4]

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.

 

[geospatial]

Thanks,

Ill give that a try and see how it goes.

 

[geospatial]

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.

 

[xenou]

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

 

[geospatial]

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.

 

[xenou]

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.