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

I'm Trying to execute Sql query left join with sum,group by and order by in MS Access.

But The result is not appropriate perhaps the sql code I am using is wrong.

Please Guide Me

Thanks

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


``
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 sql query

DATE TOTPRP TOTPRS PAYMENT TOTEXP TOTRESULT TOTOUTSTANDING TOTPROFITNET
29-08-2024 100000 150000 150000 25000 50000 0 25000
29-09-2024 400000 500000 500000 60000 100000 0 40000
29-09-2024 400000 500000 500000 60000 100000 0 40000
29-09-2024 300000 350000 350000 25000 50000 0 25000
29-09-2024 250000 300000 50000
29-09-2024 250000 300000 50000

Is it possible to apply the field dateto aperiod?

Desired result

Period TOTPRP TOTPRS PAYMENT TOTEXP TOTRESULT TOTOUTSTANDING TOTPROFITNET
Aug-24 400000 500000 500000 50000 100000 0 50000
Sep-24 900000 1100000 500000 60000 200000 -600000 -460000

Continue reading...