Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    monthly sales report (2000 SP3)

    I would like to create a sales report to show sales by item on a month by month bases for the preceding six months with an accumulated total.

    So the report would be item by Dec, Jan, Feb, Mar, Apr, May and total sales
    But if i were to run the report next month, the report would be item sales Jan, Feb, Mar, Apr, May, Jun and Total sales

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

    Re: monthly sales report (2000 SP3)

    You can create a query with the following criteria for the date field:

    Between DateSerial(Year(Date()),Month(Date())-6,1) And DateSerial(Year(Date()),Month(Date()),0)

    Use this query as record source for your report.

    Note: if you want to display the results for the months next to each other instead of below each other, you need a crosstab query and things become more complicated. Post back if you need that.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: monthly sales report (2000 SP3)

    it is a cross tab query, and i could have null values in a month.

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

    Re: monthly sales report (2000 SP3)

    You need VBA code to handle a crosstab query in a report. If you search for dynamic crosstab report in this forum, you will find several threads dealing with this subject. For example, you might have a look at the databases attached to <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in <post#=248210>post 248210</post#>.

Posting Permissions

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