MS Access equivalent query

Question

I'm attempting to write a query that I know is compatible with MySQL to MS Access. This is a programming assignment, so I'm not expecting an answer straight up given to me, but I don't know MS Access' version of SQL well enough. I wrote the MySQL myself, and tested it to be working. That's when I realized that the query needed to work for MS Access instead. So here is the known code:

SELECT `D`.`RENT_NUM`, 
    `R`.`RENT_DATE`, 
    `D`.`VID_NUM`, 
    `M`.`MOVIE_TITLE`, 
    `D`.`DETAIL_DUEDATE`, 
    `D`.`DETAIL_RETURNDATE`, 
    `D`.`DETAIL_FEE`, 
    `D`.`DETAIL_RETURNDATE` - `D`.`DETAIL_DUEDATE` AS `DAYS_LATE`
FROM `detailrental` AS `D`
    JOIN `rental` AS `R` ON `D`.`RENT_NUM` = `R`.`RENT_NUM`
    JOIN `video` AS `V` ON `D`.`VID_NUM` = `V`.`VID_NUM`
    JOIN `movie` AS `M` ON `V`.`MOVIE_NUM` = `M`.`MOVIE_NUM`
WHERE `D`.`DETAIL_RETURNDATE` - `D`.`DETAIL_DUEDATE` > 0
    ORDER BY `R`.`RENT_NUM`, `M`.`MOVIE_TITLE`;

I've been attempting to convert to MS Access SQL, but I still don't get it. Here is the most recent attempt.

SELECT [D].[RENT_NUM],
    [R].[RENT_DATE],
    [D].[VID_NUM],
    [M].[MOVIE_TITLE],
    [D].[DETAIL_DUEDATE],
    [D].[DETAIL_RETURNDATE],
    [D].[DETAIL_FEE],
    [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] AS [DAYS_LATE]
FROM [DETAILRENTAL] AS [D] INNER JOIN 
( 
  [RENTAL] AS [R] INNER JOIN 
  (
    [VIDEO] AS [V] INNER JOIN [MOVIE] AS [M] ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]
  )  ON [D].[VID_NUM] = [V].[VID_NUM]
) ON [D].[RENT_NUM] = [R].[RENT_NUM]
WHERE [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] > 0
    ORDER BY [R].[RENT_NUM], [M].[MOVIE_TITLE];

The error I receive is Syntax error in JOIN operation. I know what that means, but I don't know MS Access' SQL well enough to spot the error.

score 2 · Accepted Answer · 2014-02-17 22:00:19Z

2

It has been a while since I had to write any access query, so I just went with syntax described by official documentation http://msdn.microsoft.com/en-us/library/office/bb208854(v=office.12).aspx. Figured out it had syntax error in it. So to make query work you have to wrap JOIN with parenthesis. But each ON statement still needs to be inside each set of parenthesis not on outside. This should do the trick.

SELECT [D].[RENT_NUM]
       ,[R].[RENT_DATE]
       ,[D].[VID_NUM]
       ,[M].[MOVIE_TITLE]
       ,[D].[DETAIL_DUEDATE]
       ,[D].[DETAIL_RETURNDATE]
       ,[D].[DETAIL_FEE]
       ,[D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] AS [DAYS_LATE]
    FROM (
           (
             (
               [detailrental] AS [D] )
             INNER JOIN [rental] AS [R]
                ON [D].[RENT_NUM] = [R].[RENT_NUM] )
           INNER JOIN [video] AS [V]
            ON D.VID_NUM = [V].[VID_NUM] )
    INNER JOIN [movie] AS [M]
        ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]
    WHERE [D].[DETAIL_RETURNDATE] - [D].[DETAIL_DUEDATE] > 0
    ORDER BY [R].[RENT_NUM]
       ,[M].[MOVIE_TITLE];

edited Feb 17, 2014 at 22:00

answered Feb 17, 2014 at 21:37

user275683

Sign up to request clarification or add additional context in comments.

3 Comments

sonoftunk Over a year ago

I originally did not have any ()parentheses, but it was not working. Trying this, I receive an error Syntax error in FROM clause and it highlights the first JOIN. Using the parentheses solved(?) that problem but introduced the other. I attempted to use the parentheses based on this SO question

Tmdean Over a year ago

This answer is correct, but the set of parentheses around [detailrental] AS [D] can be removed.

sonoftunk Over a year ago

Yes, that edit is correct, and Tmdean's comment is also correct. Sometimes I wonder MS Access has to exist. I think it's terrible.

Gord Thompson · Accepted Answer · 2014-02-17 21:58:52Z

1

Since we know that the problem is with the JOINs let's start from the innermost set of parentheses and work outwards:

[VIDEO] AS [V] 
INNER JOIN 
[MOVIE] AS [M] 
    ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]

That looks fine. Let's move out one level:

[RENTAL] AS [R] INNER JOIN 
(
    [VIDEO] AS [V] 
    INNER JOIN 
    [MOVIE] AS [M] 
        ON [V].[MOVIE_NUM] = [M].[MOVIE_NUM]
)
    ON [D].[VID_NUM] = [V].[VID_NUM]

This JOIN won't work. Can you see why?

answered Feb 17, 2014 at 21:58

Gord Thompson

125k39 gold badges252 silver badges458 bronze badges

4 Comments

sonoftunk Over a year ago

Actually, I don't. I've been looking at it for a little bit, but I still don't get it. Looking at SaUce's most recent edit has made me understand better how MS Access requires the parens

Gord Thompson Over a year ago

@sonoftunk look at the table aliases in the last ON clause.

sonoftunk Over a year ago

It's that the table alias D hasn't been initialized yet? By that logic, SELECT [D].[RENT_NUM] wouldn't work either, although it does. Unless you are saying that anything in parentheses is done before anything else?

Gord Thompson Over a year ago

@sonoftunk Yes, the problem is that at this point in the evaluation of the FROM clause the alias [D] doesn't exist yet because it is in an outer set of parentheses. While I understand your reasoning re: using the [D] alias in the column list you need to remember that SQL statements are not necessarily parsed all at once. The query optimizer first needs to determine which tables are involved and how they are related, and once that's done it can move on to other details like which columns should be included in the result set.

Collectives™ on Stack Overflow

MS Access equivalent query

2 Answers 2

3 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related