Acccess Query 0 Results with Relational Table

geospatial · Sep 14, 2017

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 · Sep 15, 2017

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.

Joe4 · Sep 15, 2017

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?

xenou · Sep 15, 2017

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.

geospatial · Sep 15, 2017

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.


Adams	Tory

<caption> Prsnl Query2 </caption> <thead>
[TH="bgcolor: #c0c0c0"] Last Name [/TH]
[TH="bgcolor: #c0c0c0"] First Name [/TH]

</thead> <tbody>
</tbody> <tfoot></tfoot>

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:

<caption> Prsnl Query2 </caption> <thead>
[TH="bgcolor: #c0c0c0"] Last Name [/TH]
[TH="bgcolor: #c0c0c0"] First Name [/TH]

</thead> <tbody> </tbody> <tfoot></tfoot>

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

xenou · Sep 16, 2017

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.

geospatial · Sep 17, 2017

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.

geospatial · Sep 17, 2017

I rebuilt the tables in SQL and this code works

Code:

[FONT=Courier New][SIZE=2][COLOR=blue]SELECT[/COLOR] [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]id[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lastname[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]firstname[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]rank[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]email[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterid[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]rater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterlastname[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]rater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterfirstname[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]rater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ratermi[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]rater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]rateremail[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]srraterid[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]srRater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterlastname[/COLOR]   [COLOR=blue]AS[/COLOR] [COLOR=maroon]srRaterLastName[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]srRater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterfirstname[/COLOR]  [COLOR=blue]AS[/COLOR] [COLOR=maroon]srRaterFirstName[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]srRater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ratermi[/COLOR]         [COLOR=blue]AS[/COLOR] [COLOR=maroon]srRaterMI[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]srRater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]rateremail[/COLOR]      [COLOR=blue]AS[/COLOR] [COLOR=maroon]srRaterEmail[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]reviewerid[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]reviewer[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterlastname[/COLOR]  [COLOR=blue]AS[/COLOR] [COLOR=maroon]reviewerLastName[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]reviewer[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterfirstname[/COLOR] [COLOR=blue]AS[/COLOR] [COLOR=maroon]reviewerFirstName[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]reviewer[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]ratermi[/COLOR]        [COLOR=blue]AS[/COLOR] [COLOR=maroon]reviewerMI[/COLOR][COLOR=silver],[/COLOR] 
       [COLOR=maroon]reviewer[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]rateremail[/COLOR]     [COLOR=blue]AS[/COLOR] [COLOR=maroon]reviewerEmail[/COLOR] 
[COLOR=blue]FROM[/COLOR]   [COLOR=maroon]tbleprsnl[/COLOR] [COLOR=maroon]prsnl[/COLOR] 
       [COLOR=blue]JOIN[/COLOR] [COLOR=maroon]tblraters[/COLOR] [COLOR=maroon]rater[/COLOR] 
         [COLOR=blue]ON[/COLOR] [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterid[/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon]rater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterid[/COLOR] 
       [COLOR=blue]JOIN[/COLOR] [COLOR=maroon]tblraters[/COLOR] [COLOR=maroon]srRater[/COLOR] 
         [COLOR=blue]ON[/COLOR] [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]srraterid[/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon]srRater[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterid[/COLOR] 
       [COLOR=blue]JOIN[/COLOR] [COLOR=maroon]tblraters[/COLOR] [COLOR=maroon]reviewer[/COLOR] 
         [COLOR=blue]ON[/COLOR] [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]reviewerid[/COLOR] [COLOR=silver]=[/COLOR] [COLOR=maroon]reviewer[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]raterid[/COLOR] 
[COLOR=blue]ORDER[/COLOR]  [COLOR=blue]BY[/COLOR] [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]lastname[/COLOR][COLOR=silver],[/COLOR] 
          [COLOR=maroon]prsnl[/COLOR][COLOR=silver].[/COLOR][COLOR=maroon]firstname[/COLOR]  [/SIZE][/FONT]

Access SQL is different though

geospatial · Sep 17, 2017

https://www.dropbox.com/s/iy71toaqcwfp8vx/DB_Example.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.

Kyle123 · Sep 18, 2017

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

Kyle123 · Sep 18, 2017

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]

Acccess Query 0 Results with Relational Table

geospatial

Active Member

geospatial

Active Member

Excel Facts

Joe4

MrExcel MVP, Junior Admin

xenou

MrExcel MVP

geospatial

Active Member

xenou

MrExcel MVP

geospatial

Active Member

geospatial

Active Member

geospatial

Active Member

Kyle123

Well-known Member

Kyle123

Well-known Member

Similar threads

Forum statistics

Share this page

Acccess Query 0 Results with Relational Table

Active Member

Active Member

Excel Facts

MrExcel MVP, Junior Admin

MrExcel MVP

Active Member

MrExcel MVP

Active Member

Active Member

Active Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock