Ledger for Individual Customers

[knavlekar]

I have following tables. I have not included all the fields and all the tables
but the fields that I thought that are important for understanding the
database structure.

1) Orders:
Order ID (P.K)
Customer ID (F.K)
Employee ID (FK)
Order Date
Ship Address
Ship Date, PO number, Ship Name, Ship Address, Ship State, Ship Code, Ship
Country, Freight Charge, SalesTax Rate

2) Order Details:
Order Detail ID (PK)
Order ID (FK)
Product ID (FK)
Qty .
Unit Price

3) Payments:
Payment ID (PK)
Order ID (FK)
Payment Amount
Payment Date
Payment Mode

4) Customers:
Customer ID (PK)
Company Name and other customer details fields

5) Employee:
Employee ID & other employee details fields

6) Products:
Product ID (PK) and other product details Fileds.

I want to create Credit & Debit ledger for individual customers for specific
date range.

Can anyone help me out for making this report as well al building queries
related to this report?

Thanks

 

[Allen Browne]

You have Payments directly related to Orders, so you need to think clearly
about what you are asking for here.

Say a customer places order 99 for $200 on June 30th, and pays for it half
on July 15 and half on Sep 15. Do you want to create the ledger *by order*
or *by month*? If you select a date range, (e.g. just July), do you want to
show only the orders and payments in that period (+100 in the example
above), or do you want to show all orders that have any order or payment in
that period? If the latter, is it okay to show nothing for August (as there
were no transactions in that month), or did you want to show that the
customer had $100 outstanding in Aug, even though there were not
transactions.

As always with databases, the answer you get depends on the question you
ask.

While you are thinking that through, you might also want to consider how you
will handle cases such as:
a) a payment that covers 2 or more orders
b) pre-payments (monies received before a clear order is placed)
c) over-payments (where the customer paid more than the actual charge)
d) refunds (where you are unable to supply something that was ordered.)

 

[knavlekar]

Thank you for your reply once again.

I want two types of reports
1) Life time transcations of a particular customer (right from day one the
customer started business with us)
2) Ledger for a specific period (e.g. from 1st of April 2007 to 31st of
March 2008 in this case the report should indicate the opening balance of a
particular customer on 1st of April 2007)

Hope i am able to explain my requirements.

thanks in advance.

 

[Allen Browne]

Use a form where the user enters the dates for the period you want to report
on. The form will have 2 unbound text boxes (say, txtStartDate and
txtEndDate), and a command button that opens the report.

The report that lists any orders and any payments in that period, based only
on the OrderDate and PaymentDate, regardless of whether the payment is for
an order in that period or not. You will need a main report for the orders,
and a subreport for the payments. The queries for these reports will read
the dates from the form, e.g.:
Between [Forms].[Form1].[txtStartDate] And Between
[Forms].[Form1].[txtEndDate]

The remaining task is to get the correct opening balance to show in the
CustomerID group header section. You can then add it to the new orders,
subtract the total payments, and get the closing balance.

The opening balance will be an expression that uses a pair of DSum()
expression to get all prior orders and all prior payments. Example to get
the sum of previous orders for the customer:
=DSum("[Qty] * [Unit Price]", "qryOrdersWithDetail", "([Customer ID] = "
& Nz([Customer ID], 0) & ") AND ([Order Date] < " &
Format([Forms].[Form1].[txtStartDate], "\#mm\/dd\/yyyy\#") & ")")