SQL query left join with sum,group by and order by in MS Access

I'm trying to execute a SQL query with a left join with sum, group by and order by clauses in MS Access.

But the result is not correct - perhaps the SQL code I am using is wrong.

Table Expense

ID  DATE        INVONO      TRANSACTION TOTEXP
----------------------------------------------
1   29-08-2024  EXP-1001    EXPENSE 25000
2   30-08-2024  EXP-1002    EXPENSE 25000
3   29-09-2024  EXP-1003    EXPENSE 30000
5   29-09-2024  EXP-1004    EXPENSE 30000

Table Invoice

DATE        INVONO      TRANSACTION    TOTPRP   TOTPRS  PAYMENT
---------------------------------------------------------------
29-08-2024  SALES-1000  SALES           100000  150000  150000
30-08-2024  SALES-1001  SALES           300000  350000  350000
29-09-2024  SALES-1002  SALES           200000  250000  250000
29-09-2024  SALES-1003  SALES           200000  250000  250000
30-09-2024  SALES-1004  SALES           250000  300000  
30-09-2024  SALES-1005  SALES           250000  300000  

My query:

SELECT 
    Invoice.Date AS [DATE],
    SUM(Invoice.TotPRP) AS TOTPRP, 
    SUM(Invoice.TotPRS) AS TOTPRS, 
    SUM(Invoice.PAYMENT) AS PAYMENT, 
    SUM(Expense.Totexp) AS TOTEXP, 
    SUM(Invoice.TotPRS) - SUM(Invoice.TotPRP) AS TOTRESULT,
    SUM(Invoice.PAYMENT) - SUM(Invoice.TotPRS) AS TOTOUTSTANDING,
    SUM(Invoice.PAYMENT) - (SUM(Invoice.TotPRP) + SUM(Expense.TotEXP)) AS TOTPROFITNET
FROM 
    Invoice 
LEFT JOIN 
    Expense ON Invoice.Date = Expense.Date
GROUP BY 
    Invoice.Date, Invoice.INVONO
ORDER BY 
    Invoice.Date;

Result from my SQL query:

Is it possible to apply the field date to a period?

Desired result

You need to GROUP BY the date range you want to sum.

In your case, you want to sum the data per month, so you will use FORMAT(Invoice.[DATE], 'MMM-yy') to build exactly the date range you requested.

This will then be put to the column selection, to the GROUP BY clause and also to the ORDER BY clause. The entire query will be this:

SELECT 
    FORMAT(Invoice.[DATE], 'MMM-yy') AS Period,
    SUM(Invoice.TotPRP) AS TOTPRP, 
    SUM(Invoice.TotPRS) AS TOTPRS, 
    SUM(Invoice.PAYMENT) AS PAYMENT, 
    SUM(Expense.Totexp) AS TOTEXP, 
    SUM(Invoice.TotPRS) - SUM(Invoice.TotPRP) AS TOTRESULT,
    SUM(Invoice.PAYMENT) - SUM(Invoice.TotPRS) AS TOTOUTSTANDING,
    SUM(Invoice.PAYMENT) - (SUM(Invoice.TotPRP) + SUM(Expense.TotEXP)) AS TOTPROFITNET
FROM 
    Invoice 
LEFT JOIN 
    Expense ON Invoice.[DATE] = Expense.[DATE]
GROUP BY 
    FORMAT(Invoice.[DATE], 'MMM-yy')
ORDER BY 
    FORMAT(Invoice.[DATE], 'MMM-yy');

See this sample fiddle