Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems creating a Report (2000)

    Overly large screenshot moved to zipped attachment by HansV. Please don't post images over 640x480 pixels.

    I am trying to create a report to show Sales by customer by month (see attached example)
    I have tbl Customers, tbl Categories and tbl SalesOrders

    I have a report with Customer Name (from tbl Customers) and a sub report based on a query. The query includes:-
    Category Name (tbl Categories)
    Customer ID (tbl Customers) as a hidden field
    Date (tbl SalesOrders)
    Value (tbl SalesOrders)

    My Date field is actually a formula =Month([DateOfSale]) (This gives me a number 1 to 12 for my report, but I

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

    Re: Problems creating a Report (2000)

    You can fix the column headings in a crosstab query:
    - Open a crosstab query in design view.
    - Click in an empty part of the upper half of the query window.
    - Activate the Properties window.
    - Set the Column Headings property to 1;2;3;4;5;6;7;8;9;10;11;12

    To include all categories, you must create a new query based on the table that lists all categories and on the crosstab query, joined on the category with a left join: double click the join line and specify that you want to return all records from the category table.

    To display the month names, you can change the label captions manually. Or you can set them in code when the report is opened.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems creating a Report (2000)

    I now have 12 labels (Jan to Dec) in a header report and have used a crosstab query as the basis for the sub-report. The crosstab query has the column headings as you suggested and this now works well.

    However, I also need to graph the sales and it seems I am unable to create a graph on the crosstab query because my "Month" field has now split over 12 columns (named "1", "2", "3" etc) and I am only allowed 6 fields in a chart).

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

    Re: Problems creating a Report (2000)

    Try using a select query instead of a crosstab query for the graph. The Chart Wizard will create the crosstab query for you. You can edit this query (the Row Source of the graph control) later if you wish.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems creating a Report (2000)

    That's great - Thanks once again Hans. My new query is linked to a new table ("tblMonths") and I changed the join properties so as to return all months from tblMonths.

    Although one last problem if I may... My graph has "Months" along the x axis, but it only returns a column if there is sales data. I would like to see all 12 months in the graph with a zero sales point when there are no sales. I have tried using the NZ function but it still returns a blank field in the query.

    Many thanks
    Robert

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

    Re: Problems creating a Report (2000)

    What is the Row Source of your graph?

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems creating a Report (2000)

    The Row source field reads :

    TRANSFORM Sum(qrySales.Expr1) AS SumOfExpr1 SELECT tblMonths.Month FROM qrySales RIGHT JOIN tblMonths ON qrySalest.Expr1 = tblMonths.MonthID GROUP BY tblMonths.MonthID, tblMonths.Month ORDER BY tblMonths.MonthID PIVOT qrySales.[No];

    qrySales is :
    CustomerID
    Expr 1: Month([date of sale])
    Amount (

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

    Re: Problems creating a Report (2000)

    It's hard to visualize the problem. Could you post a stripped down copy of the database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems creating a Report (2000)

    Thankyou Hans
    My zipped database is attached. In rpt Customer Report, on the x axis, I would like the graph to show Jan through to December, with zero where there is no data.

    (Ideally, with each new month I would like to show the previous 12 months data in the report, rather than the fixed columns Jan through to December, as I have now. But I presume that requires a lot more work and I would be happy to get this stage under my belt for now)

    Many thanks
    Robert

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

    Re: Problems creating a Report (2000)

    See the attached version. I split the row source for the chart into two queries. (I hope it works OK in Access 2000, the chart updates correctly in Access 2002).

  11. #11
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems creating a Report (2000)

    Hans,
    Thank you very much, once again. It works perfectly. Now I just need to study your example closely, so I completely understand it myself !!

    Thanks
    Robert

Posting Permissions

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