Creating a party ledger/Account?

[darshit_goswami]

Hi,



I'm in the middle of something. I have created a database (for learning purpose only) which should maintain a retail store and its inventories along with profit and loss calculations.
I've managed to create things with purchase, purchase orders, sale orders and inventory control so far. now i m thinking to make party ledger for both suppliers(from whom i buy things) and customers (whom i sell).

Nature of business is not ideal. Payments TO Supplier and also FROM customers may come in partial payments.

Like say supplier XYZ supplied various goods worth $1000 in POID 1 PODate 01/01/2015
again he supplied goods worth $500 in POID 2 PODate 01/03/2015

Now I make payment with PaymentID 1 on paymentDate 01/02/2015 with $700

so now I want to generate a report where I should get PurchaseOrder Details POID, PODate and POAmount and also paymentID, PaymentDate and paidAmount along with remaining credit or debit.

I tried to run few queries but failed.

what should be the way?

See attached snap for ERD (partial)

[orange]

Would be better to see the entire model.
You may get some ideas/concepts from this free data model
What does this have to do with Party ??

[darshit_goswami]

Oops that seems tough for me to understand as of now.

But let me try to keep it simple about what I want.

I want to have list of All the purchase orders I made with one supplier.
I also want to have list of All the payments I made to the supplier.

Then I want to sum all orders amount and substract all paid amount , that should give me balance (cr/dr)

If anything is wrong with my data model here just let me know. Payments have nothing to do with Orders. Both are different entities with common supplier name.

I made only 1 payment, but when i run the query using joins , it gives me a scene that i made payment (same) against all POIDs. So if i make payment of 700$ once. Its counted as $1400 in total.

hope i m not confusing u,

[June7]

Build aggregate queries to summarize each then build another query to join the aggregate queries to master dataset of supplier.

[darshit_goswami]

Summarize means doing groupby queries ?

I did it already, I used it in my form too. let me show u sample.


But all I m talking is about creating a report for the same. I wonder how to ???

[June7]

If you want to show detail records, probably need report/subreport arrangement.

[orange]

Please post your query SQL.

Based on June's previous post re aggregates, you would have 3 queries along these lines:

QryReceived        select sum(GoodsValue) as RECVD from PurchaseOrders where supplier = "SUPPLIER X"  

QryPaid               select Sum(PaymentAmt) as PAYD from Payments where supplier = "SUPPLIER X"

Final query          SELECT (qryReceived.RECVD - QryPaid.PAYD) as Balance From QryReceived, QryPaid
                          WHERE QryReceived.supplier = QryPaid.Supplier

[darshit_goswami]

If you want to show detail records, probably need report/subreport arrangement.

I think I need to do little bit of more research on Reports/subreports.

Thanks for the suggestion.

@Orange : I couldnt build any query such a way that i want. These are two sub forms. Which i put together.

Query i built gives me total for each supplier.

Query1:

SELECT qryPurAmtSup.SupplierName, Sum(qryPurAmtSup.[Total Amount]) AS [SumOfTotal Amount]
FROM qryPurAmtSup
GROUP BY qryPurAmtSup.SupplierName;

Query2:

SELECT tblPayment.supplierName, Sum(tblPayment.paidAmount) AS SumOfpaidAmount
FROM tblPayment
GROUP BY tblPayment.supplierName;

Query3:

SELECT Query1.SupplierName, Query1.[SumOfTotal Amount], Query2.SumOfpaidAmount, Nz([Query1]![SumOfTotal Amount])-Nz([Query2]![SumOfpaidAmount]) AS Net
FROM Query1 LEFT JOIN Query2 ON Query1.SupplierName = Query2.supplierName;

where qryPurAmtSup:

SELECT tblPurchaseOrder.POID, tblPurchaseOrder.PODate, tblPurchaseOrder.SupplierName, Sum(tblPurOrderDetail.productTotal) AS [Total Amount]
FROM tblPurchaseOrder INNER JOIN tblPurOrderDetail ON tblPurchaseOrder.POID = tblPurOrderDetail.POID
GROUP BY tblPurchaseOrder.POID, tblPurchaseOrder.PODate, tblPurchaseOrder.SupplierName;

But still Query3 is not desired result. it gives me correct data. But its not desired one. Need to stuck my head more with this type of things.

[orange]

???
It all starts with the table design. You may have reports and subreports but they get their data from a table or a query against a table(s).

Do some testing. Get a query to get the data you think you need from a table(s). Then use that query as record source for your report.