Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Crosstab headings (2002)

    Hi,

    I have a crosstab query that is working fine. I need to create a report from it that shows [txtreportyear] as the column heading. I know how to set the headings in the query. My problem is that the report years will grow over time. Presently the years include 2002, 2003, 2004. I'm OK with adding in upcoming years in advance such as 2005, 2006 etc and having those column's empty until those years have data. My problem is that other offices using this report may have different year ranges. I don't want to have to customize the column headings for each office. Is it possible to define the column headings in the query so that the names actually populate according to the database year?

    I've enclosed the query and have converted it to 1997 to make it small enough.

    Thanks,
    Leesha

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

    Re: Crosstab headings (2002)

    >>Is it possible to define the column headings in the query so that the names actually populate according to the database year?<<
    Exactly what do you mean by this?

    How many years do you wish to go back from the latest year in the table tblCorporateSalesTrending?

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Hi Pat,

    In the sample I posted, the database year starts with 2003. If I were simply making the report for our offices, I'd start my column headers with 2003 and go from there. The issue is that the year the report starts in other offices will differ. It could be 2004, it could be 1999, and so on. I have no way of knowing which is why I'm look for a way for the query or report to define that on its own vs me setting the column headingss. The reason I'm looking for a way to have the query do it is because I need to build a report from it and I can't use the report wizard without the year colunm headings.

    Thanks,
    Leesha

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

    Re: Crosstab headings (2002)

    You can use VBA code to make the report dynamic - the column headings etc. will be determined at runtime, when the report is opened. I have attached your database (in Access 2000 format) with such a report.

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab headings (2002)

    Leesha,

    You can change the years by changing the sql of the query. You has done this already.
    I add a sub in module1, ChangePivot. You'll recognize the code.
    Just before running the report use in your code :
    ChangePivot("1999,2000,2001,2002")
    or whatever years you want.

    FYI If you select Tools / Database Utilities / Compact and Repair Database, you'll be able to zip the db under the 100K and no need to convert it to 97.
    Francois

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Thanks Hans!! This doing exactly what I need it to do and the years change according to what is in the database which is what I needed!

    Leesha

  7. #7
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Hi Francois!

    Thanks for the example. I will hold onto it to learn from it. In comparing the two, the one from Hans does what I need it to do in that the years change based on the data in the table.

    Leesha

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Hi Hans,

    Back again. In the report I will need to filter the query by month. The month is pulled from the [frmCorporateSalesTrending].[cmbReportMonth]. When I enter this in the query and just run the query it is fine. When I try to open the report I get an error "Too few parameters, expected 1". How do I correct for this?

    Thanks,
    Leesha

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

    Re: Crosstab headings (2002)

    You will have to define the parameter in the query.

    A point with Hans solution is that there is only a maximum of 10 years, so a combination of Francois's and Han's solutions are required.

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Hi Pat,

    When I defined it in the query I got the error message stated above.

    Also, I need to combine Francois's and Hans's? How do I do that?

    Leesha

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

    Re: Crosstab headings (2002)

    Just a little complaint I have, you defined a problem in your first post, Hans and Francois were good enough to solve that then in post 424183 you added another condition. Hans solution would have included both had you asked. The problem with giving a problem in parts like this is that it sometimes takes a redefine by the solution provider. That's my b..tch out of the way.

    You will need to supply the parameter by way of qdf.parameter([frmCorporateSalesTrending].[cmbReportMonth])=[frmCorporateSalesTrending].[cmbReportMonth]. This should be done in VBA code.

    While you are changing the SQL of the query, this is the place to define the PIVOT clause with consecutive years from the oldest to the latest year in the database.

  12. #12
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Thank you. I will try to figure it out.

    BTW, the reason I didn't have the info in the original post was in order to make it small enough to send I had to strip it down to just about nothing so the page that the month refers to, or I should say form, was not included in the post, therefore I took it out of the query. I had no idea it would be this complicated or even possible.

    I apologize Hans and Francois. As always I'm extremely grateful for all of your help.

    Leesha

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

    Re: Crosstab headings (2002)

    That's alright, maybe I was a bit harsh, maybe it's my diabetes clicking in, sorry if I offended.
    Replace the command in Hans solution:
    Set rst = CurrentDB.OpenRecordset(Me.RecordSource, dbOpenDynaset)
    with:
    Dim qdf As DAO.QueryDef
    Set qdf = CurrentDb.QueryDefs(Me.RecordSource)
    qdf.Parameters("Forms![frmCorporateSalesTrending]![cmbReportMonth]") = Forms![frmCorporateSalesTrending]![cmbReportMonth]
    Set rst = qdf.OpenRecordset

    If you wish I will also work out the code for the years that francois suggested.

  14. #14
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab headings (2002)

    Well I sincerly hope your diabetes isn't acting up but I will admit that I just about ran for the hills never to post to the lounge again.

    I'd greatly appreciate your working on the code Francois suggested as I haven't a clue where to start but wasn't about to admit it or ask for help. Do you need me to post anything?

    Leesha

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

    Re: Crosstab headings (2002)

    I'm glad you decided to stay.
    Have a look at the report included in the database especially the OnOpen event of the report. The first part of it reads the SQL of the query and reconstructs this with respect to the WHERE clause and the PIVOT clause.

Page 1 of 3 123 LastLast

Posting Permissions

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