Acccess Query 0 Results with Relational Table

geospatial

Active Member
Joined
Sep 2, 2008
Messages
290
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.
 
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.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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?
 
Upvote 0
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:
Upvote 0
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 NameFirst Name
AdamsTory

<caption> Prsnl Query2 </caption> <thead>
</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:

Last NameFirst Name

<caption> Prsnl Query2 </caption> <thead>
</thead> <tbody> </tbody> <tfoot></tfoot>


That is why I was saying the relationship errors it out. There is not an actual error.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Access gets funny about parentheses, I think you just want:

<font face="Courier New" size="2">
<font color = "blue">SELECT</font> <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">id</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">lastname</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">firstname</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">rank</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">email</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">raterid</font><font color = "silver">,</font>
<br/>       <font color = "maroon">rater</font><font color = "silver">.</font><font color = "maroon">raterlastname</font><font color = "silver">,</font>
<br/>       <font color = "maroon">rater</font><font color = "silver">.</font><font color = "maroon">raterfirstname</font><font color = "silver">,</font>
<br/>       <font color = "maroon">rater</font><font color = "silver">.</font><font color = "maroon">ratermi</font><font color = "silver">,</font>
<br/>       <font color = "maroon">rater</font><font color = "silver">.</font><font color = "maroon">rateremail</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">srraterid</font><font color = "silver">,</font>
<br/>       <font color = "maroon">srRater</font><font color = "silver">.</font><font color = "maroon">raterlastname</font>   <font color = "blue">AS</font> <font color = "maroon">srRaterLastName</font><font color = "silver">,</font>
<br/>       <font color = "maroon">srRater</font><font color = "silver">.</font><font color = "maroon">raterfirstname</font>  <font color = "blue">AS</font> <font color = "maroon">srRaterFirstName</font><font color = "silver">,</font>
<br/>       <font color = "maroon">srRater</font><font color = "silver">.</font><font color = "maroon">ratermi</font>         <font color = "blue">AS</font> <font color = "maroon">srRaterMI</font><font color = "silver">,</font>
<br/>       <font color = "maroon">srRater</font><font color = "silver">.</font><font color = "maroon">rateremail</font>      <font color = "blue">AS</font> <font color = "maroon">srRaterEmail</font><font color = "silver">,</font>
<br/>       <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">reviewerid</font><font color = "silver">,</font>
<br/>       <font color = "maroon">reviewer</font><font color = "silver">.</font><font color = "maroon">raterlastname</font>  <font color = "blue">AS</font> <font color = "maroon">reviewerLastName</font><font color = "silver">,</font>
<br/>       <font color = "maroon">reviewer</font><font color = "silver">.</font><font color = "maroon">raterfirstname</font> <font color = "blue">AS</font> <font color = "maroon">reviewerFirstName</font><font color = "silver">,</font>
<br/>       <font color = "maroon">reviewer</font><font color = "silver">.</font><font color = "maroon">ratermi</font>        <font color = "blue">AS</font> <font color = "maroon">reviewerMI</font><font color = "silver">,</font>
<br/>       <font color = "maroon">reviewer</font><font color = "silver">.</font><font color = "maroon">rateremail</font>     <font color = "blue">AS</font> <font color = "maroon">reviewerEmail</font>
<br/><font color = "blue">FROM</font>   <font color = "maroon">(</font><font color = "maroon">(</font><font color = "maroon">(</font><font color = "maroon">prsnl</font> <font color = "maroon">prsnl</font>
<br/>          <font color = "blue">LEFT</font> <font color = "blue">JOIN</font> <font color = "maroon">tblraters</font> <font color = "maroon">rater</font>
<br/>                 <font color = "blue">ON</font> <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">raterid</font> <font color = "silver">=</font> <font color = "maroon">rater</font><font color = "silver">.</font><font color = "maroon">raterid</font><font color = "maroon">)</font>
<br/>         <font color = "blue">LEFT</font> <font color = "blue">JOIN</font> <font color = "maroon">tblraters</font> <font color = "maroon">srRater</font>
<br/>                <font color = "blue">ON</font> <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">srraterid</font> <font color = "silver">=</font> <font color = "maroon">srRater</font><font color = "silver">.</font><font color = "maroon">raterid</font><font color = "maroon">)</font>
<br/>        <font color = "blue">LEFT</font> <font color = "blue">JOIN</font> <font color = "maroon">tblraters</font> <font color = "maroon">reviewer</font>
<br/>               <font color = "blue">ON</font> <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">reviewerid</font> <font color = "silver">=</font> <font color = "maroon">reviewer</font><font color = "silver">.</font><font color = "maroon">raterid</font><font color = "maroon">)</font>
<br/><font color = "blue">ORDER</font>  <font color = "blue">BY</font> <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">lastname</font><font color = "silver">,</font>
<br/>          <font color = "maroon">prsnl</font><font color = "silver">.</font><font color = "maroon">firstname</font> 
</font>
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top