Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summary report (Access 2000)

    i need help to build a report that shows the sales of the customer each year.
    I have an excellent report showing the sales of each customer per month.However this report shows only the sales for a given year.And i want to break down the sales for each year.
    For example
    Customer A
    2002
    Januray 10 liters
    Februray 20 liters
    Totalo for 2002
    2003
    January 29 liters
    february 30 liters
    Total for 2003
    I attach 2 reports. Report 1 is the working for a given year and report2 is what i have done to achieve my aim.
    In report2 in the Sorting and grouping i have added invoicedate GroupOn year. In the invoice date header i have added =DatePart("yyyy";[InvoiceDate])
    In spite if that my report does not divide the sales into years, does not sum them up and repeats the liters for a given month.
    Can somebody have a look in my attached report and tell me where i am wrong?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary report (Access 2000)

    The tables in your database are empty, and some lookup tables are missing. That makes it impossible for us to see what the reports do. Could you repost it with a few dummy records?

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Thank you for your reply.Iam sending attached the dummy tables.I am waiting for your help

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary report (Access 2000)

    The main point is that the order of the expressions in the Sorting and Grouping window is wrong: InvoiceDate (by year) should come before MonthNumber.

    You also want subtotals by month; you can do this by copying the txtCompanyTotal text box to the MonthName group footer (and naming the copy txtMonthTotal)

    See attached version.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Thank you so much for your reply.!!!!!!!! I appeciate very much you propmt and clever
    replies.I cannot stop wondering how powerful Access is The report works excellent and gives
    a very clear picture for each customer for each year.
    Is it possible to use the same report and see only the total results instead of the
    result per months?
    For example for Customer Nr 1 to see only the following totals:
    Overview for Customer 1
    total for 2003 38
    total for 2004 86
    Total for customer1 124

    Kind regards

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary report (Access 2000)

    Remove the controls from the group header of the InvoiceDate field and from the Details section, and set the height of those sections to 0.

    Although not strictly necessary, you can set the Group Header property of the InvoiceDate field to No in the Sorting and Grouping window, and remove Monthnumber from the Sorting and Grouping window.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Thank you very much for your reply.It is wonderful that we can choose between 2 possiblitiers with one and the same report.With your suggestion,can i
    choose and navigate between the two possiblities? Veru ofthen the manager looks the general figures and sometimes we may want to delve into the
    details of the customer and see more details, e.e. the months breakdown.Therefoe the 2 alternatives must always be at hand even to alternate.WHat do you think ios the best wau, to ask a question, or to place 2 buttons?
    I have a problem with my report which is due to my query, i suppose.The problem is that noiw i get more than one figure for
    a given month,as you can see from the attachment.How can i sum up all the quantities for the mont in the summary ?

    Kind regards

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Summary report (Access 2000)

    Just as you have a group level for the year, you can add a group level for the month.

    In the attached database, I have added a small form on which the user can select to view the summary by month or by year. The On Open event of the report uses this to hide the monthly summary if necessary.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Dear Hans,

    Thank you so much for all your replies.I possess now a magic tool enabling us to
    have a quick glance at the status of the customer, and if we want, to drill down further and see the movements of its sales by month.
    I would recommend this thread to everybody dealing in similar matters

    Kind regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •