Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reports from Pivot Table (Access 2002)

    Am having to come to grips with Pivot tables and believe I am getting the better of them. However, to present this to the world, I would normally haul the "query" into a report. This does not seem to be possible? Trying to print the the original Pivot Query is not good because the page setup does not stick! Creating a Pivot Form overcomes that problem but I cannot figure out how to give the form (printed or otherwise) a title, footer, etc.
    Can someone please point me in the right direction or explain what I can/cannot do.
    Thanks

    John

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

    Re: Reports from Pivot Table (Access 2002)

    A pivot table, query or form is interactive, a report is static, so they don't go together well. Two alternatives:

    1. Create a pivot table in Excel based on the same data (you don't have to import the data into Excel first). Excel is much better at printing pivot tables.
    2. Create a crosstab query and then a report based on the crosstab query. It is possible to create crosstab reports with dynamic column headings, see for example <post#=296915>post 296915</post#>.

    The nearest you can come with a pivot table in Access itself is to use the pivot form as subreport in a report with the desired headers and footers. The subreport won't resize itself to fit the contents, however.

  3. #3
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    Thanks Hans
    The example will be useful in another situation, but at this stage the crosstab query to report seems the best option, although I am aware that crosstabs are "on the way out". Maybe Access2003 will enable the same thing for Pivot tables??
    I am actually working on an upgrade of an existing program. The original authors used the crosstab query/report option, but the user had to go into the query and change the criteria before running the report. I have created a selector form with 4 different fields for selection. At this point I run into trouble. The crosstab query runs off another query, which is the one with the criteria selected from the form. Yes, I have set the Parameters in the crosstab the same as the criteria in the first query, but do not get any data returned when I run the crosstab.
    Any thoughts please.

    Thanks

    John

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

    Re: Reports from Pivot Table (Access 2002)

    Why would crosstab queries be on the way out?

    Can you post the SQL for both the parameter query and the crosstab query based on it? (If you open a query in design view, then select View | SQL, you can copy the SQL string of the query to the clipboard.)

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    Hi Hans, prompt reply!!
    re crosstab queries - reading Special Edition Using MS Access2002, it was suggested that as crosstab is not html/.net compliant they would not probably continue!

    Subquery:
    SELECT DISTINCTROW Format$([COMPLAINT_DETAILS].[DATE],"mmm yyyy") AS [Month/Year], [Item Type].ITDescription AS [Part Type], Sum(COMPLAINT_DETAILS.[NUMBER OF COMPLAINTS]) AS [Sum], Year([COMPLAINT_DETAILS].[DATE])*12+DatePart("m",[COMPLAINT_DETAILS].[DATE])-1 AS Expr1
    FROM (COMPLAINT_DETAILS LEFT JOIN PRODUCT ON COMPLAINT_DETAILS.[Item No] = PRODUCT.[Item No]) LEFT JOIN [Item Type] ON PRODUCT.[Item Type] = [Item Type].[Item Type]
    WHERE (((COMPLAINT_DETAILS.COUNTRY)=[Forms]![Selector]![Country1]) AND ((PRODUCT.[Item Type])=[Forms]![Selector]![Part Type1]) AND ((COMPLAINT_DETAILS.DATE)>DateAdd("m",-12,[Forms]![Selector]![FDate1]))) OR (((COMPLAINT_DETAILS.COUNTRY)=[Forms]![Selector]![Country1]) AND ((COMPLAINT_DETAILS.DATE)>DateAdd("m",-12,[Forms]![Selector]![FDate1])) AND (([Forms]![Selector]![Part Type1]) Is Null))
    GROUP BY Format$([COMPLAINT_DETAILS].[DATE],"mmm yyyy"), [Item Type].ITDescription, Year([COMPLAINT_DETAILS].[DATE])*12+DatePart("m",[COMPLAINT_DETAILS].[DATE])-1
    ORDER BY Year([COMPLAINT_DETAILS].[DATE])*12+DatePart("m",[COMPLAINT_DETAILS].[DATE])-1;

    Crosstab:
    PARAMETERS [Forms]![Selector]![Country1] Text ( 255 ), [Forms]![Selector]![Part Type1] Text ( 255 ), [Forms]![Selector]![FDate1] DateTime;
    SELECT DISTINCTROW ComplaintssummaryPT.[Month/Year], ComplaintssummaryPT.[Part Type], Sum(ComplaintssummaryPT.Sum) AS SumOfSum, ComplaintssummaryPT.Expr1
    FROM ComplaintssummaryPT
    GROUP BY ComplaintssummaryPT.[Month/Year], ComplaintssummaryPT.[Part Type], ComplaintssummaryPT.Expr1
    ORDER BY ComplaintssummaryPT.Expr1;

    Thanks

    John

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

    Re: Reports from Pivot Table (Access 2002)

    Wow! That doesn't make for easy reading. Some remarks:

    1. Unless you need the subquery for other purposes, I would remove the sort order. It is not relevant for the second query, and it slows things down.

    2. Am I missing something? The second query is another totals query, not a crosstab query - there is no PIVOT field.

  7. #7
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    Sorry, although I still have the same problem if it is a select query or a Pivot. No return of any data as soon as I put the Parameters in.

    Here is the Crosstab for completeness although may not need it:
    PARAMETERS [Forms]![Selector]![Country1] Text ( 255 ), [Forms]![Selector]![Part Type1] Text ( 255 ), [Forms]![Selector]![FDate1] DateTime;
    TRANSFORM Sum(ComplaintssummaryPT.Sum) AS SumOfSum
    SELECT ComplaintssummaryPT.[Month/Year]
    FROM ComplaintssummaryPT
    GROUP BY ComplaintssummaryPT.[Month/Year], ComplaintssummaryPT.Expr1
    ORDER BY ComplaintssummaryPT.Expr1
    PIVOT ComplaintssummaryPT.[Part Type];

    Parameters:
    [Forms]![Selector]![Country1] Text
    [Forms]![Selector]![Part Type1] Text
    [Forms]![Selector]![FDate1] Date/Time

    John

    PS May not come back quickly this time: it is 1:36 on Friday afternoon here so we are off for a long weekend!!!!!!

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

    Re: Reports from Pivot Table (Access 2002)

    You have opened the Selector form and filled in appropriate values in Country1, Part Type1 and FDate1 when you open the second query, I hope?

  9. #9
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    Hans
    It was a great weekend away, but reality says I have to complete this job. Refreshed, I looked at the problem again and recreated the queries from scratch. Now the crosstab, and later the pivot table all work.

    I am going to use the Pivot Form to report on the information in a grid format, although it would be nice if one could put a title on the page.
    The Pivot Chart is better in that respect. Both these views have 12 months of data across the top, ie a fixed width so I can guarantee getting them on the page when they are printed.
    When I have more time I will look at using your code with a crosstab query and variable headings.

    Do you know if Access 2003 has any improvements for reporting/printing out using pivot tables?

    Thanks again
    John

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

    Re: Reports from Pivot Table (Access 2002)

    I haven't seen Access 2003 (or anything of Office 2003), but the information about "what's new in Access 2003" on http://office.microsoft.com doesn't mention pivot forms or charts.

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

    Re: Reports from Pivot Table (Access 2002)

    That's because they aren't new to Access 2003 and because they haven't made any significant changes in them.
    Charlotte

  12. #12
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    Thanks for info on Access 2003. Maybe I just need to understand all the possibilities for crosstabs and pivot tables/forms. The learning curve has been steep the last few days.
    As stated in previous posts, I have a subquery feeding a crosstab. The subquery derives some parameters from a selector form. Two of these are required all the time, but one, Part Type, either has a value or is null for all. The subquery works fine for both cases. As soon as I try the crosstab with a Null Part Type, it returns no records. With Part Type equal to a value, it returns the expected record.
    A previous post shows what I have put in the Parameters form in the crosstab. What am I missing that it won't work with a Null Part Type??

    John

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

    Re: Reports from Pivot Table (Access 2002)

    Could you post a stripped-down version of your database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave just enough records to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Compact the database (Tools | Database Utilities > Compact and Repair Database).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If it is still too large, convert to Access 97 format (Tools | Database Utilities | Convert Database | To Prior Database Version...) and zip the converted database.
    <LI>Attach the zip file to a reply.[/list]Perhaps we'll be able to see what causes the problem.

  14. #14
    Star Lounger
    Join Date
    May 2002
    Location
    Auckland, North Island, New Zealand
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reports from Pivot Table (Access 2002)

    here is the stripped down version.
    Open form "Selector'
    Click on "Crosstab Test" - No records returned
    Enter "Bread" or "Kisses" in the "Part Type" combo and the record is returned.
    Why don't I get all part type records returned when I leave the Part Type combo blank?

    Thanks

    John
    Attached Files Attached Files

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

    Re: Reports from Pivot Table (Access 2002)

    Thanks for posting the database. I don't think I would have found the cause and solution for the problem without looking at the actual queries.

    The problem is that you must specify the form parameters explicitly, otherwise the crosstab query will prompt for them. But specifying [Forms]![Selector]![Part Type1] as Text (which seems logical, since it contains text values) excludes Null values - Null is not text.

    The solution is to specify [Forms]![Selector]![Part Type1] as Value, i.e. untyped. This will make the crosstab query handle Null values correctly.

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
  •