Results 1 to 4 of 4
  1. #1
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100

    INNER JOIN vs LEFT OUTER JOIN

    I have a query that joins 3 tables. I want the query to return ALL of the TOP 50 records in the first table as long as they have matching DowntimeEventID's and also the Services that have matching serviceID's from the 2nd and 3rd tables. The problem is that the query doesn't return the rows from the first table (DowntimeEvent) that do not have matching ServiceID's. I suspect it has to do with the INNER JOIN and that it should be a LEFT OUTER JOIN. But I get an error that this type of join is not supported. I am using Access 2013. Can anyone help me? My query is as follows:



    SELECT TOP 50 DowntimeEventService.DowntimeEventID, DowntimeEventService.ServiceID, Service.Description, DateValue(DowntimeEvent.BeginDate) AS [Begin Date], TimeValue(DowntimeEvent.BeginDate) AS [Begin Time], DateValue(DowntimeEvent.EndDate) AS [End Date], TimeValue(DowntimeEvent.EndDate) AS [End Time], DowntimeEvent.Description, DowntimeEvent.Planned, DowntimeEvent.ReportedBy, DowntimeEvent.ServerName, DowntimeEvent.UsePhysicalServer

    FROM DowntimeEvent INNER JOIN (DowntimeEventService INNER JOIN Service ON DowntimeEventService.ServiceID = Service.ServiceID) ON DowntimeEvent.DowntimeEventID = DowntimeEventService.DowntimeEventID

    ORDER BY DowntimeEvent.BeginDate DESC;
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    In Access it's just LEFT, not LEFT OUTER

  3. #3
    shylock is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2018
    Location
    Dayton, Ohio
    Posts
    100
    Thanks, Ajax. I modified the query to read LEFT JOIN but get a syntx error on the FROM statement. New Query is shown below. Where am I going wrong?

    SELECT TOP 50 DowntimeEventService.DowntimeEventID, DowntimeEventService.ServiceID, Service.Description, DateValue(DowntimeEvent.BeginDate) AS [Begin Date], TimeValue(DowntimeEvent.BeginDate) AS [Begin Time], DateValue(DowntimeEvent.EndDate) AS [End Date], TimeValue(DowntimeEvent.EndDate) AS [End Time], DowntimeEvent.Description, DowntimeEvent.Planned, DowntimeEvent.ReportedBy, DowntimeEvent.ServerName, DowntimeEvent.UsePhysicalServer

    FROM (DowntimeEvent LEFT JOIN DowntimeEventService on DowntimeEvent.DowntimeeventID = DowntimeEventService.DowntimeEventID) (INNER JOIN Service ON DowntimeEventService.ServiceID = Service.ServiceID)

    ORDER BY DowntimeEvent.BeginDate DESC;

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Your bracketing is wrong in the FROM part of the statement. Use the query builder and compare

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-23-2014, 03:07 PM
  2. Left OUTER Join to more than one table?
    By oemar00 in forum Queries
    Replies: 3
    Last Post: 09-20-2013, 03:58 PM
  3. Left-Outer Join on Non-Unique ID
    By defaultuser909 in forum Queries
    Replies: 2
    Last Post: 09-06-2012, 10:16 AM
  4. Replies: 4
    Last Post: 09-03-2012, 04:53 PM
  5. Outer left join leaves out null values
    By mschles4695 in forum Queries
    Replies: 1
    Last Post: 12-22-2010, 11:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums