Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    21

    Question Unanswered: Crosstab Queries....

    Ok, new question:

    I have a report I need to run each week. Here's the way I need to have it set up:

    ................................| 9/16/04 | | 9/9/04 | | 9/2/04 |
    ................................|Labor|NL|ODC| |Labor|NL|ODC| |Labor|NL|ODC|
    | Proj # | Description |

    So for each week I need to be able to see how many dollars were spent, by project, by element of cost (labor, non-labor, etc)

    Is there a way I can tweak the crosstab query to let me have 2 column headings? Or any other way I can do this??

    Unfortunately, it HAS to be set up this way... boss's orders!

  2. #2
    Join Date
    Jul 2004
    Posts
    21

    Question

    Any ideas guys? The formatting didn't come out correctly, so I'm gonna try it again:

    ....................................| 9/16/04 |........| 9/9/04 |.........| 9/2/04 |
    ................................|Labor|NL|ODC| |Labor|NL|ODC| |Labor|NL|ODC|
    | Proj # | Description |

    I hope that helps a little more... I desperately need ideas, so whatever you can suggest would be a great help!

    Thanks again!!!

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi,
    You can't cross-tab to give heirarchical headings asaik. However, you can build a query in such a manner that the data will be presented as you want it. The idea is you manufacture a new column which will represent the column he headings.

    Here's an example cross-tab query:

    Code:
    TRANSFORM Sum(Costs.Cost) AS SumOfCost
    SELECT Costs.ProjectNo
    FROM Costs
    GROUP BY Costs.ProjectNo
    PIVOT CStr(Left([Date],5)) & " " & [CostElement];
    Your column headings will look like:
    9/9 Labor....9/9 NL.... 9/9 ODC....9/16 Labor....9/16 NL.... 9/16 ODC....

    My query used a table called Costs with fields:
    ProjectNo
    CostElement
    Date
    Cost

    You can play around with the CStr(Left([Date],5)) & " " & [CostElement] bit to get it to look as neat as possible.

    HTH
    Chris

  4. #4
    Join Date
    Jul 2004
    Posts
    21
    Great idea... I didn't even think of that... here goes nothin'!

    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
  •