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 Bill.*, SumPmts
FROM Bill LEFT JOIN (SELECT BillNos, Sum(Amt) AS SumPmts FROM Payment GROUP BY BillNos) AS Pmts ON Bill.BNos = Pmts.BillNos
WHERE FinalAmt>Nz(SumPmts,0);

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

 

[Shyamdharam2]

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.

Can you Just make the query in my sample database
I am Unable to make it will be where kind of you..

 

[June7]

Can you just copy/paste the SQL statement into SQL View of query builder?

Actually, I had wrong SQL in prior post. I have now edited it. Look again. However, should have been simple for you to correct it.

 

[Shyamdharam2]

Thanks for your Help, it works

can you give me some tips for (late payment interest calculation) query

or to track some kinds of remainder for late payment query..

 

[June7]

No.
I have never had to build a db for business accounting. I have only used off-the-shelf accounting programs.
I suspect calculating interest/late fee will require VBA and be rather complicated.

 

[Shyamdharam2]

thanks for your help

 

[arnelgp]

I corrected my query to this:

SELECT *, TOTALBILL-NZ(TOTALPAYMENT,0) AS BALANCE
FROM (SELECT bill.customerid, bill.Bnos, First(bill.finalamt) AS TotalBill, Sum(payment.amt) AS TotalPayment
FROM bill LEFT JOIN payment ON bill.Bnos = payment.billnos
GROUP BY bill.customerid, bill.Bnos) WHERE  TOTALBILL-NZ(TOTALPAYMENT,0)>0

 

[June7]

Seems to be more complicated than need be. Any advantage to your structure?

 

[Mark_]

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

Important question for your business accounting, do you apply any payment to the oldest outstanding debt first OR do you allow funds to be allocated to specific bills?

If the first, this becomes easy. If the second, it becomes complicated.

Also, how many bills and payments do you expect per customer account? 10's, 100's, 1000's?

Final question, and one you'll want to check with the finance people or, do you have a posting date for revenues reported to the government? If so, you'll end up with a separate file that holds the amounts purchased and amounts paid as of the end of the period for each customer so you can track any deferred revenue.

If my questions don't make sense to you, sit down with who ever is the book keeper for the business and work through exactly HOW the business is currently not only tracking this but how they also report to the government. You may also need to make sure you have some kind of journal entry system to annotate corrections to past payments or bills that were changed AFTER the end of a reporting period.

Goal here is to make sure no one from the government comes looking for YOU because the business is paying less than what the government says they should, but the business is putting the blame on you.

 

[arnelgp]

Any advantage to your structure?

i think the sub-query is done once.

 

[June7]

And isn't that so in my version (posts 4 and 7)?

 

[Shyamdharam2]

i think the sub-query is done once.

Can Pls Give Me Little Help in Above Query

To Find Branch Wise Oustanding Report Sample Data Below

 

[mike60smart]

Can Pls Give Me Little Help in Above Query

To Find Branch Wise Oustanding Report Sample Data Below

Hi
Can you give us an example of what you are expecting to see

 

[June7]

So why are you using wrong query? Why is there no report?