Avatar of Rob4077
Rob4077
Flag for Australia asked on

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;
Microsoft Access

Avatar of undefined
Last Comment
Rob4077

8/22/2022 - Mon
SOLUTION
teebon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob4077

ASKER
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'.
teebon

Hi Rob,

I missed the operator "ON" earlier. I've edited my reply, can you try again?
Rob4077

ASKER
Have you changed it? Same error still comes up.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
teebon

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob4077

ASKER
Still getting "Syntax error (missing operator) in query expression 'blJobDetails.EticLineNum = tblNumbers.LineNum  LEFT JOIN tblJobs ON tblJobs.Jobnum = tblJobDetails.fkJobNu'.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
Nothing attached
Rob4077

ASKER
Sorry, what attached file? Have I missed something?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
MS Access says "Join not supported"
teebon

SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
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?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rob4077

ASKER
Dale, ignore last message about the parameter, it was just a spelling error - your solution works.
Rob4077

ASKER
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?
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Dale Fye

What version of Access are you using?  I'm getting an "unrecognized database format" error when I try to open the file.
Rob4077

ASKER
I'm using 2013.
Rob4077

ASKER
Here's a copy as an mdb, Hopefully this will work
Temp.mdb
Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
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????
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob4077

ASKER
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