Include all records in SQL Output

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;
Rob4077Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

teebonProduct ManagerCommented:
Hi Rob,


Try this instead:

SELECT tblJobDetails.EticPageNum, tblNumbers.LineNum, tblJobDetails.ActivityDocumentNum, tblJobs.Description, tblJobs.Phases, tblJobDetails.MeterNumber, tblJobs.PowerDomeLoc, tblJobDetails.fkActivityId
 FROM 
 tblNumbers LEFT JOIN tblJobDetails ON tblJobDetails.EticLineNum = tblNumbers.LineNum  LEFT JOIN tblJobs 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; 

Open in new window

0
Rob4077Author Commented:
Tried that but I get a "Syntax error (missing operator) in query expression 'blJobDetails.EticLineNum = tblNumbers.LineNum  LEFT JOIN tblJobs ON tblJobs.Jobnum = tblJobDetails.fkJobNu'.
0
teebonProduct ManagerCommented:
Hi Rob,

I missed the operator "ON" earlier. I've edited my reply, can you try again?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rob4077Author Commented:
Have you changed it? Same error still comes up.
0
teebonProduct ManagerCommented:
SELECT tblJobDetails.EticPageNum, tblNumbers.LineNum, tblJobDetails.ActivityDocumentNum, tblJobs.Description, tblJobs.Phases, tblJobDetails.MeterNumber, tblJobs.PowerDomeLoc, tblJobDetails.fkActivityId
 FROM 
 tblNumbers LEFT JOIN tblJobDetails ON tblJobDetails.EticLineNum = tblNumbers.LineNum  
 LEFT JOIN tblJobs 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; 

Open in new window

0
Rob4077Author Commented:
Still getting "Syntax error (missing operator) in query expression 'blJobDetails.EticLineNum = tblNumbers.LineNum  LEFT JOIN tblJobs ON tblJobs.Jobnum = tblJobDetails.fkJobNu'.
0
teebonProduct ManagerCommented:
Hi Rob,

Looks like the query is getting cut.  Can you try running the attached file instead?
0
Rob4077Author Commented:
Nothing attached
0
Rob4077Author Commented:
Sorry, what attached file? Have I missed something?
0
Pratik MakwanaData AnalystCommented:
use INNER JOIN insted of left or right join....

SELECT tblJobDetails.EticPageNum, tblNumbers.LineNum, tblJobDetails.ActivityDocumentNum, tblJobs.Description, tblJobs.Phases, tblJobDetails.MeterNumber, tblJobs.PowerDomeLoc, tblJobDetails.fkActivityId
FROM tblJobs INNER 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;
0
Rob4077Author Commented:
MS Access says "Join not supported"
0
teebonProduct ManagerCommented:
0
Pratik MakwanaData AnalystCommented:
you need to divide your query like this....

Example :
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees INNER JOIN Suppliers ON Employees.City = Suppliers.City
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees LEFT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE (((Suppliers.City) Is Null))
UNION ALL
SELECT Employees.LastName, Employees.City, Suppliers.CompanyName, Suppliers.City
FROM Employees RIGHT JOIN Suppliers ON Employees.City = Suppliers.City
WHERE Employees.City is null;
0
Dale FyeCommented:
Do you ever have more than 8 details?  If not, and you want all 8 records from tblNumbers associated with each Job, even when there are not 8 details in tblJobDetails, then you are going to need a Cartesian join between tblJobs and tblNumbers.  Try:

SELECT tblJobDetails.EticPageNum
, JL.LineNum
, tblJobDetails.ActivityDocumentNum
, JL.Description
, JL.Phases
, tblJobDetails.MeterNumber
, JL.PowerDomeLoc
, tblJobDetails.fkActivityId
FROM (
SELECT tblJobs.*, tblNumbers.LineNum
FROM tblJobs, tblNumbers) as JL
LEFT JOIN tblJobDetails
ON JL.JobNum = tblJobDetails.fkJobNum 
AND JL.LineNum = tblJobDetails.EticLinNum

Open in new window

0
Rob4077Author Commented:
Hi Teebon, your approach only results in one line, same as mine.
Dale, your solution asks for a parameter value for tbljobdetails.EticLineNum. I would like to fix but I don't understand the syntax well enough.
Pratik and Dale, which is the better solution, a UNION query or a Cartesian join?
0
Rob4077Author Commented:
Dale, ignore last message about the parameter, it was just a spelling error - your solution works.
0
Rob4077Author Commented:
Ok Dale, I have tried your approach but still can't get it to list 8 records where there are not 8 records with the same EticPageNum. Have I missed something or am I better off going with the Union Query to see if I can get it to work?
0
Dale FyeCommented:
@Rob,

Can you post what you tried?  The subquery that uses the cartesian join should give you 8 records for every jobID.  Try cutting that out:

SELECT tblJobs.*, tblNumbers.LineNum
FROM tblJobs, tblNumbers
ORDER BY tblJobs.JobNum, tblNumber.LineNum

And running that by itself, you should see 8 identical lines (except for the line number) for each Job.  If you still don't have 8 records for each job after joining to tblJobDetails, it is because you are using an INNER JOIN instead of the LEFT JOIN I recommended.
0
Rob4077Author Commented:
I've tried creating a tiny version of the database and including your suggested Query1 and Query2. The output I need is for EticPageNum = 2 and it should list Lines 1-8 but only lines 1 and 2 have any data in tblJobDetails
Temp.accdb
0
Dale FyeCommented:
What version of Access are you using?  I'm getting an "unrecognized database format" error when I try to open the file.
0
Rob4077Author Commented:
I'm using 2013.
0
Rob4077Author Commented:
Here's a copy as an mdb, Hopefully this will work
Temp.mdb
0
Dale FyeCommented:
OK, I'm confused.  I added a couple of fields from the subquery JL, to your Query1, and it is obvious that there are 8 LineNum values for each JobNum.  The problem you have is that your JobDetails table does not contain an EticLineNum value for most of the records, and in actuality, there are 10 records in tblJobDetails for the '510Charlie' job number, not the 8 that you asked about.

Take a look at the Query1 I've enclosed, making no changes other than adding the JobNum and EticLineNum to the query, and changing the sort order.
Rob4077.mdb
0
Rob4077Author Commented:
Ok, I think I must be tackling this in totally the wrong way.

What I am trying to achieve is:
  1) tblJobDetails is a table which records multiple Activities associated with each job
  2) For two of those activities (let's stick with just one, say Sleep) I need to create a document (report) that lists up to 8 Sleep's associated with different jobs (Activity = "Sleep")
  3) After a user has entered a batch of data they run a small function that loops through the table and allocates an EticPageNumber (incremented by 1 once it have allocated up to 8 EticLineNumbers to the page) and an EticLineNumber (incremented from 1 to 8) to every record in tblJobDetails.
4) My report can then be printed by page with up to 8 lines in each report.

The problem arises when there are less than 8 entries in the last page of a batch of entries so I end up with less than 8 EticLineNumbers on a page.
For example In the attached sample database the only EticLineNumbers on EticPage 2 are 1 and 2.

so the output I need is something like
Page   Line   Activity   ...
   2         1      Sleep
   2         2      Sleep
   Null    3      Null    
   Null    4      Null
   Null    5      Null
   Null    6      Null
   Null    7      Null
   Null    8      Null

That way I can print a report with 8 lines. Any ideas welcome. or should I run with the Union query?

I hope this explains what I am after????
0
Rob4077Author Commented:
Figured it out, thanks to all your prompting. It would be better if I could have it all in one query instead of two but this works. What I've done is got the first query to select only the record(s) I need from the combination of tblJobs and tblJobDetails.
SELECT qryJobsAndDetails.EticPageNum, qryJobsAndDetails.ActivityDocumentNum, qryJobsAndDetails.Description, qryJobsAndDetails.Phases, qryJobsAndDetails.MeterNumber, qryJobsAndDetails.PowerDomeLoc, qryJobsAndDetails.fkActivityId, qryJobsAndDetails.EticLineNum
FROM qryJobsAndDetails
WHERE (((qryJobsAndDetails.EticPageNum)=[Forms]![frmJobsAndDetails]![cboSelectedPage] Or (qryJobsAndDetails.EticPageNum) Is Null) AND ((qryJobsAndDetails.fkActivityId)=2));

Next I use that as a source query for my final query. The final query links the above query with tblNumbers and gives me one line number for each entry even if there are no corresponding records in the other table.
SELECT tblNumbers.LineNum, qryMeterRequestByPage.*
FROM tblNumbers LEFT JOIN qryMeterRequestByPage ON tblNumbers.LineNum = qryMeterRequestByPage.EticLineNum
ORDER BY tblNumbers.LineNum;

I am awarding points based on input because all of your inputs forced me to work out the solution.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob4077Author Commented:
All contributors lead me to the solution. My comment is included as it contains the final solution which may be useful to others looking at this question
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.