2

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

halfer
  • 19,824
  • 17
  • 99
  • 186
SMA Creations
  • 23
  • 3
  • 1
    This involves two one-to-many relationships. Including all three tables in one query and then aggregating data will not provide correct results. Build two queries that aggregate Purchases and Payments then RIGHT JOIN those two queries to Suppliers. – June7 Jan 02 '21 at 10:13
  • Alternative is DSum domain aggregate function. – June7 Jan 02 '21 at 10:21
  • Are all suppliers in Ledger but just not always with an opening balance? – June7 Jan 02 '21 at 19:27
  • Why is there an opening balance - opening balance of what? – June7 Jan 02 '21 at 19:52

1 Answers1

1

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")
Thetafinity
  • 35
  • 1
  • 9
  • You show LEFT JOIN in Query2, not RIGHT JOIN. Instead of ISNULL() can use NZ(). As I understand, all suppliers would be in Ledger just not all have an opening balance. Suppliers should be unique in Ledger so GROUP BY should not be needed in Query2. And SELECT ID from Ledger. – June7 Jan 02 '21 at 19:26
  • Edited my answer to fix the LEFT JOIN and NZ() suggestion - good catches @June7! If OP clarifies what exactly he means with respect to the data in Ledger_Suppliers I'll update my answer accordingly, but for now I'll leave my solution as is. – Thetafinity Jan 02 '21 at 23:28