Re: Ledger for Individual Customers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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\#") & ")")

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"knavlekar" <knavlekar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C9E1CCF7-2063-4842-AEA6-A30203CA57FE@xxxxxxxxxxxxxxxx
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" wrote:

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"knavlekar" <knavlekar@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8FEEFEEC-B23C-43E9-87CB-F1D8D4CC9DBF@xxxxxxxxxxxxxxxx
>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



.

Relevant Pages

  • Re: Report based on calculations of 2 records
    ... Is there no other way to generate the report from my existing records? ... When a customer makes a payment, ... PayFrom and PayTo on reports. ...
    (microsoft.public.access.reports)
  • Re: Subreport not displaying all info
    ... same information for the same time-frame as the main report. ... the subreport will only contain records from the last ... If you want to show a range of payment dates in the subreport, ... If I change the link to reflect customer number, ...
    (microsoft.public.access.reports)
  • Re: Ledger for Individual Customers
    ... If you select a date range,, do you want to show only the orders and payments in that period, 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, or did you want to show that the customer had $100 outstanding in Aug, even though there were not transactions. ... Employee ID ... Ship Date, PO number, Ship Name, Ship Address, Ship State, Ship Code, Ship ...
    (microsoft.public.access.reports)
  • Re: Subreport not displaying all info
    ... If I change the link to reflect customer number, then I only get the last ... invoice amount, payment amount, payment date. ... The subreport is in the report footer. ...
    (microsoft.public.access.reports)
  • Re: Ledger for Individual Customers
    ... Life time transcations of a particular customer (right from day one the ... a payment that covers 2 or more orders ... Ship Date, PO number, Ship Name, Ship Address, Ship State, Ship Code, Ship ... Can anyone help me out for making this report as well al building queries ...
    (microsoft.public.access.reports)