How to add a field from another table to a left join query MS Access

Question

I have a query that involves both left join and not exists statements. Thus this query is not supported in design view. I would like to add a field "comments" from another table "Table2". Is this possible with the query I have created? The code is as follows:

SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement
FROM Table1 AS t LEFT JOIN
     Table1 AS tprev
     ON (tprev.id = t.id) AND (tprev.Date_ < t.Date_)
WHERE not exists 
        (select 1
        from Table1 AS t1
        where 
            t1.ID = t.ID
            and t1.Date_ < t.Date_
            and t1.Date_ > tprev.Date_);

This is my join attempt: SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement, t3.Comments FROM Table1 AS t LEFT JOIN Table1 AS tprev ON (tprev.id = t.id) AND (tprev.Date_ < t.Date_) JOIN Table2 AS t3 ON t.NUM = t3.Comments WHERE not exists (select 1 from Table1 AS t1 where t1.ID = t.ID and t1.Date_ < t.Date_ and t1.Date_ > tprev.Date_) Error is "syntax error (missing operator) in query expression '(tprev.id = t.id) AND (tprev.Date_ < t.Date_) Join Table2 AS t3 ON t.NUM = t3.Comment" — carmelle, Oct 24 '19 at 12:56
You need parentheses if you use multiple joins and you can't nest them in all ways, depending on their join-type. Take a look here https://stackoverflow.com/a/20929533/7658533 and here https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/left-join-right-join-operations-microsoft-access-sql — AHeyne, Oct 24 '19 at 13:18
From the second link: "A LEFT JOIN or a RIGHT JOIN can be nested inside an INNER JOIN, but an INNER JOIN cannot be nested inside a LEFT JOIN or a RIGHT JOIN. See the discussion of nesting in the INNER JOIN topic to see how to nest joins within other joins." — AHeyne, Oct 24 '19 at 13:19
I used both the links and the information you provided. It still isn't quite working and I'm getting a syntax error. Here is what I have: SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement FROM ((Table2 AS t2 INNER JOIN Table1 AS t ON (t2.Comments = t.ID)) LEFT JOIN Table1 AS tprev ON tprev.Date_ < t.Date_) AND (tprev.id = t.id)) WHERE not exists (select 1 from Table1 AS t1 where t1.ID = t.ID and t1.Date_ < t.Date_ and t1.Date_ > tprev.Date_); — carmelle, Oct 28 '19 at 22:13

AHeyne · Accepted Answer · 2019-10-30T06:32:46.250

0

Following your example of your last comment, this query works without any complains regarding syntax:

SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement
FROM (
     (
     Table2 AS t2
     INNER JOIN Table1 AS t
       ON (t2.Comments = t.ID)
     )
     LEFT JOIN Table1 AS tprev
       ON tprev.Date_ < t.Date_ AND tprev.id = t.id
     )
     WHERE not exists (select 1 from Table1 AS t1 where t1.ID = t.ID and t1.Date_ < t.Date_ and t1.Date_ > tprev.Date_);

The reason for the syntax error was that this parenthesis t.Date_) was too much.

If it is logically fine, you should know.

What smells a bit are those:

You compare Comments with ID. Maybe it should be CommentID?
The fieldname Date_. Maybe you can find a better name without having to use an underscore?

Update

Following the comments of this answer and the new requirements, this should be what you need:

SELECT
  t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement, t2.Comments
FROM
  (
  Table1 AS t
  LEFT JOIN Table2 AS t2
    ON t.ID = t2.ID
  )
  LEFT JOIN Table1 AS tprev
    ON (tprev.id = t.id) AND (tprev.Date_ < t.Date_)
WHERE
  NOT EXISTS (SELECT 1 FROM Table1 AS t1 WHERE t1.ID = t.ID AND t1.Date_ < t.Date_ AND t1.Date_ > tprev.Date_)

It finally is the query of your question enhanced by another left join to add the column Comments from table2 where a corresponding comment exists.

edited Oct 30 '19 at 06:32

answered Oct 29 '19 at 07:11

AHeyne

3,377
2
11
16

That is helpful, thank you. I realize I may have been unclear on how I need the query to perform. Table2 consists of two fields, "ID" and comments. Table1 has repeating IDs but table two only has one line entry for every ID. I want the comments from Table2 to match with the ID in from Table1. I will put my code in the following comment but it is not printing any values in the comments field. Hopefully this makes sense... – carmelle Oct 29 '19 at 13:45
SELECT t.NUM, t.ID, tprev.Date_ AS previous_date, tprev.Measurement AS previous_measurement, t2.Comments AS Comments FROM ( ( Table1 AS t LEFT JOIN Table2 AS t2 ON (t2.ID = t.ID) AND (t2.Comments = t.ID) ) LEFT JOIN Table1 AS tprev ON tprev.Date_ < t.Date_ AND tprev.id = t.id ) WHERE not exists (select 1 from Table1 AS t1 where t1.ID = t.ID and t1.Date_ < t.Date_ and t1.Date_ > tprev.Date_); – carmelle Oct 29 '19 at 13:45
Do you want the join to `table2` to restrict the overall result, or do you just want to add the column `Comments` from `table2` where it exists (by `ID`) to the result? – AHeyne Oct 29 '19 at 13:59
No, I do not want table2 to restrict the overall result. – carmelle Oct 29 '19 at 19:33

How to add a field from another table to a left join query MS Access

1 Answers1

Update