Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Report based on crosstab query (2003 sp2)

    I have a report based on a crosstab query with 'month' as a column heading in the query. The query displays as many months as the data reports. The report has fields for all twelve months, but gives me errors when all the months are not in the data. Do I have to create the report with code rather than designing it? And if so how would I do that? Or is there another solution?
    Thanks
    chuck

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

    Re: Report based on crosstab query (2003 sp2)

    Does the attached database do what you want, i sourced this fro Hans a while back.
    Attached Files Attached Files

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

    Re: Report based on crosstab query (2003 sp2)

    The database Pat attached shows how to create a dynamic crosstab report. If you simply want 12 columns, whether there are data or not, there is a simpler way: specify all 12 months in the Column Headings property of the crosstab query.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Report based on crosstab query (2003 sp2)

    Actually Pat, I am going to use both of Hans solutions. The columns headings are sufficient to prevent the error when the report is opened and that's really all I need, but the dynamic report I have got to do because it facinates me. I will be retiring next year so I will never get past scratching the surface of this stuff, but I have so thoroughly enjoyed it, and you guys are the best.

    Pat, Hans, thanks again.
    Thanks
    chuck

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on crosstab query (2003 sp2)

    I found this in the search (wonders will never cease!) as it fits my needs exactly. The only problem I've got is that the properties for the Column Headings brings up the Qurey properties. The other fields show their properties though?

    Ideas please.

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

    Re: Report based on crosstab query (2003 sp2)

    Sorry, I don't understand. What do you mean by "the properties for the Column Headings brings up the Query properties"?

  7. #7
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on crosstab query (2003 sp2)

    When the properties box is open, & I select the field for Column Headings, the properties box only shows the properties for the query, not the field. All other fields are OK though.

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

    Re: Report based on crosstab query (2003 sp2)

    Column Headings is a property of the query as a whole, not of an individual field. You can set this property to a comma-separated list of the column headings you want to include in the crosstab query, in the desired order. For example, if the column field for the crosstab query contains regions, you could enter the following in the Column Headings property:

    "North", "East", "South", "West"

    The crosstab query will always use these four column headings, whether there are data for each value or not, and in the specified order instead of the default alphabetic order.

  9. #9
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report based on crosstab query (2003 sp2)

    Doh! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

    Thank you for your patience.

Posting Permissions

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