Solved

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

Posted on 2016-10-19
3
33 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
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 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 47

Expert Comment

by:Dale Fye (Access MVP)
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

920 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

15 Experts available now in Live!

Get 1:1 Help Now