Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Crosstab Query - Multiple Totals (A2K SR1)

    Can a crosstab query generate multiple totals?

    Table1 contains employee hours worked with the following data fields:

    Employee, Project, Date (formatted as YYYYMM), Hours

    Users want the ability to see the total of hours worked monthly by Employee and Project with a Total Column for each Year and Grand Total.

    I figured a crosstab query would do this and it works well EXCEPT I can only get the grand total for all hours worked; I cant generate the total hours worked by year.

    Is it possible to generate yearly totals in the crosstab as well as the Grand total? If not, any ideas on how to do this. And no, users do not want a pivot table (I already have that available), they want to be lazy and get the data in a report so they don't have to think.

    Any suggestions.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Can you tell us for each field in the crosstab whether it is a row header, column header or value field? It is not entirely clear (to me at least) from your description.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Try grouping by year in your crosstab. That will allow you to generate the total hours by year. You can also do the grand total as an additional calculation.
    Charlotte

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Hans,

    The setup is as follows:

    Employee - Group by - Row Heading
    Project - Group by - Row Heading
    Date - Group by - Column Heading
    Total Hours - Sum - Row Heading
    Sum of Hours - Sum - Value

    The attached zip file shows a graphic of this. Note: PD = Date. WBS Level 1 = Project. Direct LVL 2 = Charge type, direct or indirect.

    Thanks for the help. In the interim, I will try Charlottes' idea to see if I can get it to work.
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Charlotte,

    I will give this a try but the users want to see hours by month with totals by year and a grand total. I figured that if I showed hours by year, then I would lose the view of hours by month (hopefully I am wrong).
    Regards,

    Gary
    (It's been a while!)

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

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    I think you will have to create two crosstab queries:
    - One that sums the data by month
    - One that sums the data by year
    Both queries are identical otherwise, in particular, the grouping for the row headings must be identical.
    When you are done, create a third query based on these two, and join them on the row heading fields. You can add fields from both to the query grid, thus displaying both monthly and yearly totals.

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Thanks Hans,

    Neat idea. Wish I would have thought of it.

    I will give it a try and post the results.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Hans,

    Your a genius. <img src=/S/bow.gif border=0 alt=bow width=15 height=15> Works like a charm. ...But, now a twist that I should have added to start with.

    The dataset for the crosstab queries is always changing based on user selections made. Basically, the users go to a screen and make selections via combobox to allow them to see particular subsets of data. They can select one, many, or all employees, years, projects, etc., and the the database writes the selections made to a temporary table. The database then uses the temp table to join to the main data table to reduce the amount of data to be processed as well as only let users see the data they want. If the selections made were static, then your suggestion works great.

    I guess I could write the code to generate the proper sql statement(s) to do this so it works every time. I do this for most everything else, but have never tried to write the code for a crosstab query. Your thoughts or is there a better way to do this?
    Regards,

    Gary
    (It's been a while!)

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

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Writing the SQL for a crosstab query is not that hard. Create a few crosstab queries interactively (through the wizard or "manually"), then switch to SQL view to see what the SQL looks like.

  10. #10
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Hans,

    I have already started down this path and should get this to work.

    Thanks again for the help.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Now I am stumped again.

    The crosstab queries work great and generate the correct dataset every time. Now, however, the users want to create a report with the data. The issue is that the data for period (or month) will always be changing based on what the user selects. For example, user one selects year 2004 and Jan, Feb, Mar. Thus the heading for period will be 200401, 200402, etc. The next user selects 2003 and June through Dec, so the headings are now 200306, 200307, etc.

    Any suggestions on how to get a report to work when the field names in the dataset are constantly changing? Am I making any sense? Again, a pivot table does this just fine but the users only want to have to press one button without the need to think.

    Perhaps, I just need a lot more coffee.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    If you search for dynamic crosstab report in this forum, you will find several threads dealing with this subject. For example, you might have a look at the databases attached to <post#=35485>post 35485</post#> and <post#=134439>post 134439</post#>. They demonstrate two different approaches to creating reports based on crosstab queries with dynamic column headings. Another example can be found in <post#=248210>post 248210</post#>.

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    Thanks Hans. I should have searched before asking. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,

    Gary
    (It's been a while!)

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

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    It wasn't meant as an accusation <img src=/S/smile.gif border=0 alt=smile width=15 height=15> One of the problems with searching is that you already have to know the right terms. (Same in the online help <img src=/S/aflame.gif border=0 alt=aflame width=16 height=16>)

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Crosstab Query - Multiple Totals (A2K SR1)

    No offense taken. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ... and I didn't take it as an accusation.
    Regards,

    Gary
    (It's been a while!)

Page 1 of 2 12 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
  •