Make a query of Balance/Outstanding Amount Display only

[Shyamdharam2]

i Have two table one is Bills table
another is Payment table,
I want a query that show only Payment pending amount onlye

 

[June7]

So do you assign payments to specific bills? Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Shyamdharam2]

So do you assign payments to specific bills? Provide sample data. If you want to provide db for analysis, follow instructions at bottom of my post.

Yes i Have Customer Name id in Bill Filed

-----------------------------
in Bill Table
Filed / Types
Id : Autonumber
PDate : Date Filed
BNos: Number
Customerid : Number
Place : Text Filed
Agentname : Text Filed
Gross Amt : Number
Taxes : Number
Disc : Number
Final Amt : Number

__________________________
in Payment Table
Filed / Types
Id : Autonumber
PDate : Date
Customerid : Number
Billnos : Number
Amount : Number
Agent : Text filed
Narration : Text filed


Above are the filed in the my table, I want Query that Shows

only balance pending of my bills query....

 

[June7]

You have BillNo in Payments so there is no need to put CustomerID in Payments.

Is BNos defined as primary key instead of autonumber?

Still doesn't provide data. Should I assume a bill can have multiple payments? This could get rather complicated.

SELECT tblBills.* FROM tblBills LEFT JOIN (SELECT BillNos, Sum(Amount) AS SumPmts FROM tblPayments GROUP BY BillNos) AS Pmts
ON tblBills.BNos = Pmts.BillNos WHERE GrossAmount>Nz(SumPmts,0)

 

[Shyamdharam2]

Multiple Payment is there for single bill
Sample Copy is there pls check it

 

[arnelgp]

see the query on your database.

 

[June7]

Arnel, your query is returning bill 3 as outstanding and a TotalBill amount of 5184 which is double the actual bill.

My simpler query returns only bills that have unpaid amount. To view all bills, just remove the WHERE clause. I would probably not use filter criteria in query and instead apply to report displaying records.

I modified to use FinalAmt.

SELECT tblBills.*, SumPmts FROM tblBills LEFT JOIN (SELECT BillNos, Sum(Amount) AS SumPmts FROM tblPayments GROUP BY BillNos) AS Pmts
ON tblBills.BNos = Pmts.BillNos WHERE GrossAmount>Nz(SumPmts,0)

Change db to Overlapping Windows and you can arrange objects to be viewed simultaneously. Really nice to compare query results.