Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56

    Unanswered: Dynamic Crosstab Report

    I have a crosstab query which feeds a report that shows productivity for employees for the week. The way I have been asked to set it up is that on Monday, only Monday's results will show but the dates for every day of that week show on the report with no results displayed. I have worked that part out and don't mind changing my column headers each week for the Monday-Friday dates.

    Where I am stuck is how to pass those column headings to the reports so I am not basically redoing all of the calculated fields in the group footers every week. I really would be OK with manually changing the column headers in the report each week if no way around that, but there are about 15 calculated fields in the footers which I would have to reenter formulas in and I would prefer not to do that.

    Any ideas on how to do that? I have searched long and hard to no avail.

    Thanks,
    Ben

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I have searched long and hard to no avail.
    Really?

    Google "dynamic crosstab report"

    The top TEN (at least) links explain how to create reports based on crosstab queries!

    Here's just one:
    http://support.microsoft.com/kb/328320

    Have a poke around and see what you can figure out. The first time I did this I was kinda discouraged by the seeming amount of work to do, but once you have done one, it doesn't seem all that bad.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56

    Yikes

    OMG, I read that exact article twice yesterday trying to figure it out and didn't look at it deeply enough. Now that I have actually had some sleep, it seems like that should work with some tinkering.

    FYI, that was the exact google search I did, but I got mostly SQL server stuff that on quick glance didn't seem to be applicable...maybe I should reread those as well.

    Thanks, and I will post back if I still am stuck.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good luck!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56

    Stuck at Grouping Level

    Well, I am getting closer! I have modified the code in the link you mentioned to get the report very close to what I need. What I have still not been able to figure out is how to create a group level by what is column 1 in the query.

    If I add a bound control to the group header, it displays correctly, but it won't sum columns 5-9 in my group footer. I have =sum([Col5]) in an unbound text box in the group footer, but it is not recognizing the values in Col5 in the detail section.

    I have looked at the CreateGroupLevel method but I can't seem to grasp how to reference the unbound control, Col1, in order to group it.

    Any further thoughts are greatly appreciated!

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Not sure if this will help but here is a crosstab report example I posted in the code bank:

    http://www.dbforums.com/showpost.php...5&postcount=54
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Thanks for the reply. I had actually looked at that post before I ever did my first post, but it really didn't solve what I am trying to accomplish here, namely create a crosstab report that uses parameters to build the report crosstab data and column headings.

    I now have that part worked out, but I am still trying to figure a way to get the grouping worked out. I guess to give a better example:

    Group Header: Manager
    Detail: Employees and their stats by day
    Group Footer: Summary data for the manager's group

    The code provided here has been modified so that it works with my data other than getting the grouping figured out.

    Thanks and am always willing to look at other ideas on this!

    Ben

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One thing you may consider (although I can't say if this will work with your situation but it's something I've done in the past)...

    1. Do a make-table query from the crosstab query making a "temp" table and then base the report's recordsource on that "temp" table (although with "new" fields in the crosstab, this might be a bit difficult.) But I've also been somewhat successful have a "temp" table with certain fields established and then using an append crosstab type query, append to that "temp" table. The "temp" table for example might have "Column1", "Column2", etc...for field names versus actually fields. It get's a little complex but I've had a "temp" table configured this way (and also had fields in the "temp" table which would be the "header" type names (ie. "Column1HeaderName", "Column2HeaderName", etc..) for the report.) It's difficult to explain though and I'll try to find the example on how I did this.

    Without seeing what exactly you're trying to do though, it's a little difficult. If you post the mdb, it might be a little easier to come up with a few other ideas.
    Last edited by pkstormy; 09-02-08 at 20:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    I thought about the temp table route, but then I started to think that I would have to change all of the code that I got working so far. Here is what I have. The old, hard-coded variables are in the query/report without the _New at the end. It is a copy of the actual production data, but all names have been changed to protect the innocent.

    You can see the grouping I am trying to get from the old reports and you can see where I am stuck in the _New reports. Hopefully, you can also see why the first option is a maintenance nightmare which is why I want to move to the second.

    Thanks again for your help!
    Attached Files Attached Files

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I downloaded your post but you'll need to give me a day or two to look at it. Since I just started a new job, I've got to familiarize myself with about 3 dozen new mdb's rather quickly but I'll try to come up with some suggestions for you soon.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Ugh, been there, but not with that many new DBs to wade through! Any help you can provide, whenever you can provide it is appreciated. I will continue to play with it and will let you know if I get any closer or actually figure it out.

    Thanks!
    Ben

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just looking quickly at this....

    Col5 to Col10 can't be summed by direct references because they are unbound. I think you should bind by modifying the control source of them rather than stick the values in them.

    BTW, your code:

    Code:
    Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
        
       Dim intX As Integer
        
       '  Put column headings into text boxes in page header.
       For intX = 1 To intColumnCount
          Me("Head" + Format(intX)) = rstReport(intX - 1).Name
       Next intX
    
       '  Make next available text box Totals heading.
       Me.Controls("Head" + Format(intColumnCount + 1)).ControlSource = "=""Totals"""
    
       '  Hide unused text boxes in page header.
       For intX = (intColumnCount + 2) To conTotalColumns
          Me("Head" + Format(intX)).Visible = False
       Next intX
    
    End Sub
    Has a flaw... if the board is full there is no room for the total column.

    I think the For intX = 1 To intColumnCount part should read For intX = 1 To intColumnCount - 1. This would make sure there is room to make a column for totals.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56
    Col5 to Col10 can't be summed by direct references because they are unbound. I think you should bind by modifying the control source of them rather than stick the values in them.
    I messed around with that idea for a couple of hours yesterday, and I couldn't figure out how to get the bound reference into the group footer. I like that idea though and I will play with it again to try that.

    As for the other part, I don't need row totals, only column totals. However, the code was working as it was and I was afraid to revise it too much at this point so I just hid the row total field in the detail so it wouldn't blow up. I figured that once I got everything working, I would go back and tidy things up.

    Thanks for confirming my suspicions about summing the unbound controls. I thought that was what was happening, but confirmation that I am not going completely nuts is always welcome!

  14. #14
    Join Date
    Mar 2005
    Location
    Phoenix AZ
    Posts
    56

    Not Much Closer

    Well, I've tidied up the data side of things hoping that would help, but I still can't figure out how to get the summary data into the group footer. If I go totally bound, I am stuck with changing all of the field names in the footers each week, and I guess I am just missing the boat on how to get it "semi-bound".

    Any ideas greatly appreciated! I am attaching an updated version.

    Thanks again for any input.

    Ben
    Attached Files Attached Files

  15. #15
    Join Date
    Feb 2013
    Posts
    1

    Couldn't make it work on access 2010

    Quote Originally Posted by pkstormy View Post
    Not sure if this will help but here is a crosstab report example I posted in the code bank:

    dBforums - View Single Post - DBForums Code Bank
    I downloaded your Example Database, and It run perfectly on my computer. I made an exact same report on my database, I created a Crosstab query just like yours, but when I try to open the report I get the following message: "You can't use pass-through query on a non-fixed-column crosstab query as a record source for a subform or subreport. Before you bind the subform or subreport to a crosstab query, set the query's ColumnHeadings property."

    I tried doing this, but my columns are not static, they change depending on the range of dates I select. So my columns are dynamic.

    Any ideas how to fix this problem?

    Thanks.

Posting Permissions

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