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.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
not really sure how to add part of the database but ill try.

tblPrsnl
prsnlID
firstName
lastName
raterID
srRaterID
reviewerID

<tbody>
</tbody>

tblRaters
raterID
raterFirstName
raterLastName
raterEmail

<tbody>
</tbody>

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?
 
Upvote 0
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:
RegStatusCountOfStatus
1Issue3
3Issue1
5Issue1
7Issue1

<caption> qryInventory </caption><thead>
</thead><tbody>
</tbody><tfoot></tfoot>

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

RankraterIDraterLastNameraterFirstName
SFC1CraneIchobod

<caption> qryOER </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>

raterIDraterLastNameraterFirstNameraterMI
raterRank
raterPositionraterEmail
1CraneIchobodDSupv USD
2Fett
Bobba

Unit SPT NCO
3Jobs
Steve
ETech Director
4
Beck
Robert
RInst/Writer
5Presley
Elvis
TDirector
6
McPhee
Nanny
WTng Spec
7Gates
Bill
JSupv Intel
8MartinSteve
DInstr (K9)
9Patrick
Patrick
DCG
10RamirezRobert
EDeputy

<caption> tblRaters </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>


raterIDsrRaterIDreviewerID
1310

<caption> Prsnl </caption> <thead>
</thead> <tbody>
</tbody> <tfoot></tfoot>
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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