SQL Query in MS Access even if ID is not present

user_12907113 0
Viewed 54

I've 3 tables in MS Access

  • 1st table saves the suppliers details with opening balance (may or may not be)
  • 2nd table saves the purchases made by suppliers which stores their ID and Total_Amount
  • 3rd table keeps track of the payments made by suppliers which stores their ID and Paid_Amount

I've created a query that sums the opening balance from Table 1 and Total_Amount from Table 2 and also subtracts Paid_Amount from Table 3

And it is working fine only if any payment is made the supplier name is reflecting.

I want a query which shows even if any purchase is made or not reflect the supplier name.

The Query is:

SELECT Sl_No, Supplier_Name, SUM(Opening_Balance+Total_Amount-Paid_Amount) AS TOTAL
FROM Ledger_Suppliers,  Transaction_Payments, Transaction_Purchases
WHERE Transaction_Payments.Supplier_ID = Ledger_Suppliers.Sl_No
     AND Transaction_Purchases.Supplier_ID = Ledger_Suppliers.Sl_No  
GROUP BY Sl_No, Supplier_Name

And result is:

here

sql-query-store ms-access
1 Answers

Assuming I understand your question correctly, the issue you're having is that you may not have the supplier in the Ledger_Suppliers table. What I've done below is first create a query that gets Transaction_Payments - Transaction_Purchases and then a second query that LEFT JOINs that query with the Ledger_Suppliers table. To solve your issue of not having the supplier in Ledger_Suppliers, if the value is null (ie. it's not in the table), I set its value to 0:

Nz(Ledger_Suppliers.Opening_Balance)

Similarly, I set the Supplier_Name to "NA".

Query 1:

SELECT Transaction_Payments.Supplier_ID, Sum(Transaction_Payments.Paid_Amount-Transaction_Purchases.Total_Amount) AS Total
FROM Transaction_Payments INNER JOIN Transaction_Purchases ON Transaction_Payments.Supplier_ID = Transaction_Purchases.Supplier_ID
GROUP BY Transaction_Payments.Supplier_ID;

Query 2:

SELECT SumPaymentsPurchases.Supplier_ID AS Sl_No, 
Nz(Ledger_Suppliers.Supplier_Name, "NA") AS Supplier_Name, 
SUM(SumPaymentsPurchases.Total + Nz(Ledger_Suppliers.Opening_Balance)) AS TOTAL
FROM SumPaymentsPurchases LEFT JOIN Ledger_Suppliers ON SumPaymentsPurchases.Supplier_ID = Ledger_Suppliers.Sl_No
GROUP BY SumPaymentsPurchases.Supplier_ID,  Nz(Ledger_Suppliers.Supplier_Name, "NA")
user_149237170
Related