Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2005
    Posts
    19

    Unanswered: Replicating a pivot table in access?

    I have a table containing the following fields:

    Task | Week Number | Estimated Hours | Actual Hours

    I'd like to summarize this to show task down the left, week numbers along the top and the estimated and actual hours summed, like a pivot table allowing easy comparison of both values:

    Week 1 Week 2 Week 3
    Task 1 - Estimated Hours x x x
    Task 1 - Actual Hours x x x
    Task 2 - Estimated Hours x x x
    Task 2 - Actual Hours x x x
    ... and so on




    However, having tried to use a crosstab query I realise that I can only have one value.

    Is there another way of achieving this without dragging the data into excel and creating a pivot table (which I have done and works perfectly, but is a couple of steps more than I;d like!)

    Thanks in anticipation

    Bob

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Lightbulb Pivot table

    You can try:

    New form > Autoform: Pivottable

    From within the Access interface and use the built-in wizard.

    Alternatively you can automate Excel from within access - make Excel invisible and automate a print-preview of the Pivot table so that the user doesn't even realise excel has been invoked.

    This is a little more complex so before venturing down this road, see if the wizard gives you what you need.

    If not, let me know and I will put an example up for you (this may take a day or three due to work commitments).
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Dec 2005
    Posts
    19
    Thanks Garethdart, I'll give the form a go and let you know how I get on!

    Bob

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    Hope it does the trick Bob if not, just post another reply and Ill write an example on Sat.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Oct 2009
    Posts
    4

    PIVOT function in Access?

    TRANSFORM may help you. Good luck!

Posting Permissions

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