Solved

Include all records in SQL Output

Posted on 2014-11-24
26
121 Views
Last Modified: 2014-12-04
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;
0
Comment
Question by:Rob4077
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 5
  • 4
  • +1
26 Comments
 
LVL 9

Assisted Solution

by:teebon
teebon earned 189 total points
ID: 40463870
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
 

Author Comment

by:Rob4077
ID: 40463882
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
 
LVL 9

Expert Comment

by:teebon
ID: 40463888
Hi Rob,

I missed the operator "ON" earlier. I've edited my reply, can you try again?
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Rob4077
ID: 40463898
Have you changed it? Same error still comes up.
0
 
LVL 9

Assisted Solution

by:teebon
teebon earned 189 total points
ID: 40463906
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
 

Author Comment

by:Rob4077
ID: 40463917
Still getting "Syntax error (missing operator) in query expression 'blJobDetails.EticLineNum = tblNumbers.LineNum  LEFT JOIN tblJobs ON tblJobs.Jobnum = tblJobDetails.fkJobNu'.
0
 
LVL 9

Assisted Solution

by:teebon
teebon earned 189 total points
ID: 40463933
Hi Rob,

Looks like the query is getting cut.  Can you try running the attached file instead?
0
 

Author Comment

by:Rob4077
ID: 40463943
Nothing attached
0
 

Author Comment

by:Rob4077
ID: 40463975
Sorry, what attached file? Have I missed something?
0
 
LVL 2

Assisted Solution

by:Pratik Makwana
Pratik Makwana earned 125 total points
ID: 40463979
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
 

Author Comment

by:Rob4077
ID: 40463984
MS Access says "Join not supported"
0
 
LVL 9

Expert Comment

by:teebon
ID: 40464000
0
 
LVL 2

Assisted Solution

by:Pratik Makwana
Pratik Makwana earned 125 total points
ID: 40464002
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 186 total points
ID: 40464236
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
 

Author Comment

by:Rob4077
ID: 40466328
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
 

Author Comment

by:Rob4077
ID: 40466329
Dale, ignore last message about the parameter, it was just a spelling error - your solution works.
0
 

Author Comment

by:Rob4077
ID: 40466335
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
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 186 total points
ID: 40466521
@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
 

Author Comment

by:Rob4077
ID: 40466791
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40466994
What version of Access are you using?  I'm getting an "unrecognized database format" error when I try to open the file.
0
 

Author Comment

by:Rob4077
ID: 40469882
I'm using 2013.
0
 

Author Comment

by:Rob4077
ID: 40469894
Here's a copy as an mdb, Hopefully this will work
Temp.mdb
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 186 total points
ID: 40469906
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
 

Author Comment

by:Rob4077
ID: 40469964
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
 

Accepted Solution

by:
Rob4077 earned 0 total points
ID: 40472212
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
 

Author Closing Comment

by:Rob4077
ID: 40480378
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

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question