Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot tables (Access 2000)

    Pivot tables are very versatile.
    As far as I know, in Access 2000 you can only insert them into a form so the end user has to click the "Edit PivoTable Object", mosey on to Excel, select page fields in turn, click on the "Refresh Data" button on the PivotTable toolbar, going back to Access...

    Is it possible to convert a PivotTable of an Access Form into a report?
    As it so happens in an uncannily frequent way, I'd like to attach a file to clear things out but it's 137k so, if you care to take a look, by all means, drop a line. <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

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

    Re: Pivot tables (Access 2000)

    You can also embed them in data access pages, but what kind of report did you have in mind?
    Charlotte

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables (Access 2000)

    This is what I have in mind.
    Attached Files Attached Files

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

    Re: Pivot tables (Access 2000)

    The report probably wouldn't be too hard to lay out, but you're going to have to base it on at least one crosstab query, which I always build using the wizard and then modify.

    That will give you the rough equivalent of the pivot table, although pivot tables use a hierarchical recordset that isn't *quite* like a crosstab ... or like a report recordset. I can't tell what the individual lines mean in your sample, so I can't be of more specific help.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot tables (Access 2000)

    I've followed your advice about basing the report on the crosstab query but I can only show one month at a time.
    As you can see from the text file I sent you I need to show all the months.
    The mdb file is 110k can I send it to you? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

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

    Re: Pivot tables (Access 2000)

    You can, but it would be better if you could find a way to skinny the database down enough to post here. Otherwise, some of the sense of anything I respond to will be lost on the rest of the Lounge.
    Charlotte

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks Charlotte

    I don't know what I can leave out without impinging on the file workings.
    If you can, edit my message by attaching the slimmed down file.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks Charlotte

    I create a report that looks very similar in design to your illustration. It took me a long time to get my head around it but ... I eventually got the information I needed by creating several crosstab queries and linking them in the underlying report query. I don't expect you to understand the details of the attached image but, maybe it will give you the basic idea of how I accomplished my goal. Hope it helps.
    Attached Images Attached Images
    • File Type: gif x.gif (59.4 KB, 0 views)

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Thanks Charlotte

    Giorgio sent the following to me by private post:
    <hr>Suppose I create another 11 crosstab queries quite similar to Mediegio_Crosstab query, the only difference among them being the Where clause which selects each month in turn.
    I still don't know how to make the report show each month consecutively as you see done in the text file attached to my 53216 post.
    Can you come to the rescue?<hr>
    along with a copy of the database. Unfortunately, I don't have A2K so I can't look at it, but I would make this comment: my particular solution created Crosstab Queries for each Year with Month column headings.

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How can I apply your suggestion?

    A crosstab query can only have one column heading option and, as you can see if you look at the MEDIEGIO_crosstab query, I've already specified Giorno as column heading.
    Anyone else who'd like to receive the 110k mdb? <img src=/S/grovel.gif border=0 alt=grovel width=31 height=23>

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

    Re: Pivot tables (Access 2000)

    Did you send me the file? I haven't received it yet.
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I apply your suggestion?

    The trick is in the formatting (see Attached) returns a column for each month.
    Attached Images Attached Images

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Yes, I did

    But I'll re-send it.
    Ciao

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I apply your suggestion?

    Expr1 in your attachment is indeed formatted but it still makes up the only column heading permissible and, as previously stated, I already set Giorno(whose data type is numeric ranging from 1 to 31) as column heading and, as a matter of fact, I want a day, not a month, for each column, as you can see from the text file attached to my 53216 post.

  15. #15
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How can I apply your suggestion?

    Sorry about that, got wrapped up in my own solution and completely forgot your original need. <img src=/S/sad.gif border=0 alt=sad width=15 height=15> Let me work on it a little more. You're probably right about 12 Crosstab queries.

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
  •