MS Access SQL query to LEFT join two derived tables

Question

I am relatively new to SQL and have been struggling to make following query work in MS Access. I have researched a lot, but could not find a solution. In the following query, the first sub-query creates a derived table that generates Year and Month for a given year. The second sub-query creates a derived table that outputs sum of time-in-lieu and pay-on-cheque overtime for a given employee for all the months of a year. I have tested the second sub-query and it works well.

My problem is that I am not able to successfully implement LEFT join for both the derived tables. Basically, I want to generate a table that has year and months and corresponding sum of overtimes as shown below:

Desired Output

I have tried following SQL query. It doesn't seem to be working. I tried tweaking the position of the brackets in the SELECT statement, but to no avail. I am always getting error "SYNTAX error in FROM clause".

Below is the code:

SELECT Table1.yYear1, Table1.mMonth1, TOvertime2.[Total_Time_in_Lieu], TOvertime2.[Total_Pay_on_cheque]
FROM
         SELECT yYear1, mMonth1
         FROM ( 
                    SELECT 2023 AS yYear1, 'January' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'February' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'March' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'April' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'May' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'June' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'July' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'August' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'September' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'October' AS mMonth1
                    UNION SELECT 2023 AS yYear, 'November' AS mMonth1
                    UNION SELECT 2023 AS yYear1, 'December' AS mMonth1
                    ) Table1

LEFT JOIN (
                SELECT Employee_ID, FirstName, LastName, yYear, mMonth,
                SUM(IIF(OT_Time_in_Lieu = "Yes" AND Approval_Status = "Approved", OT_total_worked, 0)) AS [Total_Time_in_Lieu],
                SUM(IIF(OT_Pay_on_cheque = "Yes" AND Approval_Status = "Approved", OT_total_worked, 0)) AS [Total_Pay_on_cheque]
                FROM TOvertime
                WHERE Employee_ID = "000510205"
                GROUP BY Employee_ID, FirstName, LastName, yYear, mMonth) TOvertime2

ON Table1.yYear1 = TOvertime2.yYear AND Table1.mMonth1 = TOvertime2.mMonth

GROUP BY yYear, mMonth;

Access does not allow you to UNION table-less SELECT statements. See [Table-less UNION query in MS Access](https://stackoverflow.com/q/7933518/77335) — HansUp, Apr 24 '23 at 13:18
Thanks a lot HansUp. The post helped me understand the work-around. — Abhishek Kumar, Apr 25 '23 at 17:08

nbk · Accepted Answer · 2023-04-24T15:52:55.320

You need to make a subquery that has all the columns.

Then you need to group by and SuM the data columns

SELECT yYear1 AS Ausdr1, mMonth1 AS Ausdr2, Sum([Total_Time_in_Lieu]) AS SUM_Total_Time_in_Lieu, Sum([Total_Pay_on_cheque]) AS SUM_Total_Pay_on_cheque
FROM (SELECT yYear1, mMonth1,Employee_ID, FirstName, LastName,[Total_Time_in_Lieu],[Total_Pay_on_cheque]
         FROM ( 
                    SELECT 2023 AS yYear1, 'January' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'February' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'March' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'April' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'May' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'June' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'July' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'August' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'September' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'October' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear, 'November' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    UNION SELECT 2023 AS yYear1, 'December' AS mMonth1 FROM  [MSysObjects] WHERE Name = 'Modules'
                    ) Table1

        LEFT JOIN (
                        SELECT Employee_ID, FirstName, LastName, yYear, mMonth,
                        SUM(IIF(OT_Time_in_Lieu = "Yes" AND Approval_Status = "Approved", OT_total_worked, 0)) AS [Total_Time_in_Lieu],
                        SUM(IIF(OT_Pay_on_cheque = "Yes" AND Approval_Status = "Approved", OT_total_worked, 0)) AS [Total_Pay_on_cheque]
                        FROM TOvertime
                        WHERE Employee_ID = "000510205"
                        GROUP BY Employee_ID, FirstName, LastName, yYear, mMonth) TOvertime2

        ON Table1.yYear1 = TOvertime2.yYear AND Table1.mMonth1 = TOvertime2.mMonth
)  AS t
GROUP BY yYear1, mMonth1;

result

Hi, thanks for your reply. But I am still getting _"Query input must contain at least one table or query"_ error. Not sure why. The query seems to be correct. — Abhishek Kumar, Apr 24 '23 at 12:36
ok i reproduced your table, and run the query, the basic problem is your time table, access doesn't allow in that way., but i changed it, so that it runs — nbk, Apr 24 '23 at 15:51
Thank you so much. Your edited answer perfectly aligned with what @HansUp noted in the comment to the original question. Your solution worked like a charm. Your explanation also helped me solve some other issues I had. — Abhishek Kumar, Apr 25 '23 at 17:07

MS Access SQL query to LEFT join two derived tables

1 Answers1