Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Pivot Tables (Access 2000)

    I am just getting to grips with Pivot Tables in Excel, and have now found a use for them in Access. I cannot find a way to put these pivot's into a report though, only a form. Is there a way of doing more with Pivot Tables in Access to make then "look pretty" or giving them a date range to search, for example? Or is it all done in Excel when I "Edit the Pivot Table Object"??? (I have an Access manual, but it only mentions Pivot Tables a couple times!!)
    Any little tips would be gratefully recieved!

  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)

    Why would you want to put them in a report? They're interactive in a form, but reports are static, which limits the usefulness of pivot tables.
    Charlotte

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

    Re: Pivot Tables (Access 2000)

    As Charlotte wrote, pivot tables are great for interactive use, but reports aren't interactive.

    You can create a crosstab query in Access that resembles a pivot table in Excel (minus the interactivity). You can base a report on a crosstab query.

    Note: when the underlying data change, the column headings of the crosstab query may change. This wll cause a standard report to break. There are several solutions for creating crosstab reports with dynamic column headings. If you do a search in this forum on "crosstab report" or "dynamic crosstab report", you'll find several threads dealing with that, for instance those starting at <post#=133447>post 133447</post#> and <post#=9413>post 9413</post#>.

  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (Access 2000)

    Basically, I ain't no expert on Access - I have pretty much taught myself all I know!! I take the points about the interactive issue. I have got my pivot table displaying what want and how i want it - I can click on "Edit Pivot Table Object" and print what I want from there. But it wont allow me to refresh the data - my pivot table is not valid. I have seached through "help" and it ain't none (!) - do I have to set up the pivot table differently to allow refreshes or is there something I do to the one I have set up to let it do it??
    (I have a table showing Location in the left column, "Complaint" "Enquiry" etc as the rows, and this is split by month goingfrom left to right.....I want the latest month to be shown, or old ones back for comparison....how??? I cn't even select from the dropdowns on the column headings)
    If this sounds a bit much - could you sugesst where to look in the help menu as half the time it's knowing what to ask it!)
    Thanks again!

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

    Re: Pivot Tables (Access 2000)

    Access 97 has several items on pivot tables in the help index. I don't know about Access 2000, but from what I've read in this forum, online help is almost cetainly worse.

    If you set up the pivot table form with the Pivot Table Wizard, you should be able to refresh the data - click the Edit Pivot Table button, then (in Excel) select Data/Refresh Data or click the red ! button on the Pivot Table toolbar. If this doesn't work, you probably should create the form anew.

    AFAIK, you can't supply a date range or something like that - Excel doesn't "understand" parameter queries. If I'm wrong (I hope so in this case!), perhaps somebody with more experience will jump in. I would still suggest using crosstab queries for that - they can be parameter queries.

  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)

    What do you mean it won't let you refresh the data from there? Are you not seeing the Pivot Table toolbar, is the refresh data button grayed out, or are you receiving an error of some kind?
    Charlotte

  7. #7
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (Access 2000)

    Hi again - took a couple days to reply as we've had a public holiday in the UK. Put this out my mind for a day or two!!
    Anyway, I can see the red ! and when I click it I get an error saying "Problems Obtaining Data". I have also had "Pivot Table not Valid" error, but the help on this is as clear as mud...something to do with OLAP and Web Pages. I am not working with a Web Page (nor ever likely to!!!) and have no idea if my data is OLAP or not. I would start again, but as I used the wizard, i will just create it in exactly the same way.

  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: Pivot Tables (Access 2000)

    Regarding Pivot tables.

    Our users are always requesting different parameters and the need for a pivot table to analyze the data. The method I am using is stable and works well. Use a form to allow the users to select the necessary parameters. Place a command button that executes a query to extract the data. (I use a make table query, although not necessary, to populate a table to hold the data.) In your code, then launch the pivot table form based on the data that was extracted.

    One thing you might also check is in VB Mode, Go to Tools References, and make sure OLE Automation is checked.
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Lounger
    Join Date
    Oct 2001
    Location
    Bristol, Bristol, United Kingdom
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pivot Tables (Access 2000)

    Thanks for your help - I think I will try another method in the future!!
    I have (I think!) got it working now!!! As HansV said before, the Pivot table doesn't seem to like a parameter query - i did have parameters in the original query, even though i wasn't using them (I'd copied and pasted from another query) - as soon as I deleted the column with it in it worked! I am still having problems, as i copied and pasted from the backup i was working in into the original and it then wouldn't work!! Then playing with the copy i turned it into an object and can't turn it back!!! Why does Access have self destruct buttons??? Anyway, I think I'm OK now as I know it can be done - I'll just start again!!!
    As Arine says though.....I'll be back!!!

Posting Permissions

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