Start Free Trial

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;

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

Hi Rob,

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

ASKER

Have you changed it? Same error still comes up.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Still getting "Syntax error (missing operator) in query expression 'blJobDetails.EticLineNum = tblNumbers.LineNum LEFT JOIN tblJobs ON tblJobs.Jobnum = tblJobDetails.fkJobNu'.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Nothing attached

ASKER

Sorry, what attached file? Have I missed something?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

MS Access says "Join not supported"

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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?

ASKER

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

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

ASKER

I'm using 2013.

ASKER

Here's a copy as an mdb, Hopefully this will work
Temp.mdb

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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