CODEWITHSUNDEEP
sqlms-access
Chong
Chong

Reputation: 13

MS Access: Left Join does not return all the rows in the left table

Sample data: enter image description here

SELECT tblStudent.student_id,
       [monthly_fee]+[book_fee] AS [Total Fee],
       Sum(tblReceipt.receipt_amount) AS SumOfreceipt_amount,
       [monthly_fee]+[book_fee]-Nz(Sum([tblReceipt]![receipt_amount]),0) AS Outstanding,
       tblReceipt.month,
       tblReceipt.year
FROM tblStudent
LEFT JOIN tblReceipt ON tblStudent.student_id = tblReceipt.student_id
GROUP BY tblStudent.student_id,
         [monthly_fee]+[book_fee],
         tblReceipt.Description,
         tblReceipt.month,
         tblReceipt.year
HAVING (((Sum(tblReceipt.receipt_amount))>0)
        AND ((tblReceipt.Description)='Total Fee')
        AND (([monthly_fee]+[book_fee]-Nz(Sum([tblReceipt]![receipt_amount]),0))>0)
        AND ((tblReceipt.month)=[Forms]![frmDialogMonth3]![cb_Month])
        AND ((tblReceipt.year)=[Forms]![frmDialogMonth3]![txt_Year]))
OR (((Sum(tblReceipt.receipt_amount)) IS NULL))
ORDER BY tblStudent.student_id;

Hi,

Please find the attached sample table with data and the sql statement above. I want to create a report to show students that have not pay and those who have not fully paid their total fee (description = Total Fee) for that month, including their outstanding amount for that month.

The result I got only show students that have not fully paid their total fee with the outstanding amount, plus students that do not have any records in the tblReceipt. Those students with records in tblReceipt other than for Total Fee did not appear in the result.

Please find the sample result below.

Result

Upvotes: 0

Views: 246

Answers (1)

Mark Farmiloe
Mark Farmiloe

Reputation: 396

You can't do any filtering on tblReceipt if it is Left Joined.

You need to first build a query to return the total fee receipts for each student in the relevant month:

SELECT student_id, Sum(receipt_amount) AS FeeReceipts
FROM tblReceipt
WHERE [description]='Total Fee' AND [month]=[Forms]![frmDialogMonth3]![cb_Month] AND [year]=[Forms]![frmDialogMonth3]![txt_Year]
GROUP BY student_id;

I called it MonthFeeReceipts. Then use this in the master query:

SELECT tblStudent.student_id, monthly_fee, book_fee, FeeReceipts, [monthly_fee]+[book_fee]-Nz([FeeReceipts],0) AS Outstanding
FROM tblStudent LEFT JOIN MonthFeeReceipts ON tblStudent.student_id = MonthFeeReceipts.student_id;

Upvotes: 1

Related Questions