troubleshooting Question

Include all records in SQL Output

Avatar of Rob4077
Rob4077Flag for Australia asked on
Microsoft Access
26 Comments9 Solutions166 ViewsLast Modified:
I am using the following SQL to extract data from a couple of tables.
tblNumbers is a fixed table with one record for each number between 1 and 8.
I ALWAYS need 8 records output even if there is no data in the linked tables.
I thought this code would do it but it will only list all 8 records from tblNumbers if there are matching records in tblJobDetails.
Is there any way I can get this to work the way I need?

SELECT tblJobDetails.EticPageNum, tblNumbers.LineNum, tblJobDetails.ActivityDocumentNum, tblJobs.Description, tblJobs.Phases, tblJobDetails.MeterNumber, tblJobs.PowerDomeLoc, tblJobDetails.fkActivityId
FROM tblJobs RIGHT JOIN (tblJobDetails RIGHT JOIN tblNumbers ON tblJobDetails.EticLineNum = tblNumbers.LineNum) ON tblJobs.Jobnum = tblJobDetails.fkJobNum
WHERE (((tblJobDetails.EticPageNum)=2 Or (tblJobDetails.EticPageNum) Is Null) AND ((tblJobDetails.fkActivityId)=2 Or (tblJobDetails.fkActivityId) Is Null))
ORDER BY tblNumbers.LineNum;

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 9 Answers and 26 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 9 Answers and 26 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros