Results 1 to 10 of 10
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Report with graph and 2 subreports (Access 2003 SP2)

    I have a main report with a graph and 2 crosstab subreports.
    The main report is printed per Area (there are 1 to 7 Areas), with a graph dependant on the Area and also 2 crosstab subreports that are dependant on the Area.
    Area 1 may have stores (column headings) that are different to Area 2.
    I need to print a page per Area showing the graph and the 2 subreports that relate to the appropriate Area, a text box on the main report named textArea.

    Can I just use the Master/Child links linked to a text box on the main report named textArea, or, do I put a where clause in the crosstab queries that relate to the text box on the main report?
    It seems if I just use the Master/Child links I get 52 Stores columns which is the total of all areas.

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

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    I assume that you have code in the subreports to set the columns as discussed in a previous thread. The recordset you open in this code must take the linkage to the main report into account, so it might be best to put criteria referring to the main report in the crosstab query.

    If you have set the Column Headings property of the crosstab query, the columns will always be the same, regardless of whether data are present or not.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    By the column headings property, do you mean the PIVOT clause?

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

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    If you open a crosstab query in design view, click in an empty part of the upper half of the query window, then activate the Properties window, you'll see a Column Headings property. To quote from the corresponding help subject:
    <hr>You use the ColumnHeadings property to obtain more control over the appearance of column headings in a crosstab query. By default, Microsoft Access displays all data values as column headings in ascending order. For example, if your Column Heading field name is Month, the default column headings displayed will be April, August, December, February, and so on. You can use the ColumnHeadings property to display the data in the correct order with the appropriate setting: "January", "February", "March", and so on.

    If you include a column heading in the ColumnHeadings property setting, the column is always displayed in query Datasheet view, even if the column contains no data. This is useful for a report based on a crosstab query, for example, when you always want to display the same column headings in the report.<hr>
    In the SQL of the crosstab query, it looks like this:

    ...
    PIVOT columnfield In ("value1","value2","value3");

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    <P ID="edit" class=small>(Edited by patt on 13-Feb-06 22:11. Blind as a bat)</P>That's what I thought you meant, the PIVOT clause. I cannot find the Column headings property in the property tab when you right click on the upper grey area of the query in design view. Don't worry about it, I found it, cannot see the wood for the trees.
    The problem with what you say is that the crosstab returns 52 columns when all I need for Area 1 say, is just 7 columns (something in each column).
    For area 2 there is 5 columns, and so it goes for the other areas.

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

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    Could you post a stripped down & zipped copy of the database?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    The latest problem I have is the attached error. How do I overcome this?

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

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    Oops, I forgot about that (although it came up twice late in 2005). Subreports bound to a crosstab query with dynamic column headings don't work. I don't know of a way around it offhand, but if you post a sample, somebody might come up with an idea.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    I had a feeling that was the problem. I will have to try another approach to this.

    I'm sure it can be done even with dynamic column headings, but not the way I was approaching it.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Report with graph and 2 subreports (Access 2003 SP2)

    I decided to go with populating a table and so do away with the 2 subreports being replaced with 1 subreport which does the job nicely.

    This turned out quite well in the end with all the complication being in the form that populates the table.

    Thanks for your help Hans.

Posting Permissions

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