Solved

Include all records in SQL Output

Posted on 2014-11-24
26
115 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now