?
Solved

Creating a query to show all records from one table plus those that are linked from another

Posted on 2016-10-19
3
Medium Priority
?
55 Views
Last Modified: 2016-10-19
I think my requirement should be quite simple, but just cannot seem to figure it out.

I have two related tables - Drivers and Journeys.
I am trying to construct a query that will list ALL drivers and then details of their future [date>=Date()] journeys if they have any.

Maybe like this....
Joe Bloggs
John Smith - Journey1
John Smith - Journey2
Jack Green
Jim Black - Journey3
Fred White - Journey4
Fred White - Journey5
etc etc...

All my attempts so far will only list drivers if they have a journey.

Hope someone can give me a nudge in the right direction.

Regards
Richard
0
Comment
Question by:rltomalin
[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
  • 2
3 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye earned 2000 total points
ID: 41849890
Try something like:

Selecte Drivers.*, J.*
FROM Drivers
LEFT JOIN (
SELECT Journeys.* FROM Journeys
WHERE [Date] > Date()
) as J ON Drivers.DriverID = J.DriverID
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41849895
to expand on the previous comment,  in order to get all of the drivers, you have to use a LEFT Join to the Journeys table, but once you assign a criteria to the Journeys table, you end up losing all of the drivers who don't have future journeys.  You might also be able to do:

Selecte Drivers.*, Journeys.*
FROM Drivers
LEFT JOIN Journeys ON Drivers.DriverID = Journeys.DriverID
WHERE (Journeys.[Date] IS NULL) OR (Journeys.[Date] > Date())
0
 

Author Closing Comment

by:rltomalin
ID: 41850214
Hi Dale
The first option works fine - thanks very much.
The second option was similar to my attempts, where the journey criteria limited the list to drivers with journeys.

I think I can work with the first option - will have to build on it now.

Regards
Richard
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

770 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