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

    Rearranging a Query Result Set (2003)

    Upon selecting 2005 in the startup form of the attached mdb, I'd like to prepare a report showing the data you see in the Query24 query with the rows and columns swapped. I thought of preparing a union query joining 4 queries, one for each column in the Query24 query. The problem is the result set in the component queries is not on the same row, see for example Query25. Is there a way to put all the data from Query25 on the same row?

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

    Re: Rearranging a Query Result Set (2003)

    You can use 4 crosstab queries, and a union query. See attached version.

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

    Re: Rearranging a Query Result Set (2003)

    Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Rearranging a Query Result Set (2003)

    If I don't embed the YearlyProd form in the Report1 report, the four rows in the Query25 result set get displayed on the same page of the Report1 report but they don't if I embed it. How can I make it so the Report1 report shows all the four rows on the same page?

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

    Re: Rearranging a Query Result Set (2003)

    Put the subform in the page header section or in the report header section.

    BTW You don't need a group header for Cat. You can put the text boxes in the detail section.

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

    Re: Rearranging a Query Result Set (2003)

    Thank you Hans,
    I cannot display the legend to the pivot chart because its captions cannot be edited so as a workaround I've put a rectangle in the detail section of the Report1 report but how do you make the colour of the rectangle match the colour of the chart bars?

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

    Re: Rearranging a Query Result Set (2003)

    I don't know how to do this with the default colours, but in the attached version, the custom colors used in the chart are displayed in the rectangles.

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

    Re: Rearranging a Query Result Set (2003)

    Thank you Hans, how would you go about modifying the pivot chart so that it shows the progressive values by month for the same series?

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

    Re: Rearranging a Query Result Set (2003)

    Open the pivot chart form.
    Select Pivot Chart | By Row/By column, or click the By Row/By column button on the Pivot Chart toolbar.
    Close the form (changes will be saved automatically).

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

    Re: Rearranging a Query Result Set (2003)

    I'm sorry Hans, I guess I didn't properly explain myself. I meant that the x axis should still show the months with four bars for each month but the value in each bar is cumulated month after month. Please see example in the attached file.

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

    Re: Rearranging a Query Result Set (2003)

    Use this query as record source for a new pivot chart form.

    SELECT qryProdScalve.Espr1, MonthName([Espr1]) AS Mese, Val(DSum("ProdMaz","qryProdScalve","Espr1<=" & [Espr1])) AS ProdMaz, Val(DSum("ProdDez","qryProdScalve","Espr1<=" & [Espr1])) AS ProdDez, Val(DSum("Tot","qryProdScalve","Espr1<=" & [Espr1])) AS Totale, Val(DSum("Tot","qryProdScalvex","Espr1<=" & [Espr1])) AS TotaleAP
    FROM qryProdScalve;

    See attached version (I didn't bother putting in chart and axis titles)

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

    Re: Rearranging a Query Result Set (2003)

    Awesome!! Thank you Hans, that query is a masterpiece <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

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

    Re: Rearranging a Query Result Set (2003)

    I've found out if you open the Report1 report and try to print it, nothing gets printed. Do you know why?

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

    Re: Rearranging a Query Result Set (2003)

    The problem is that the variable intIndex keeps its value. During the formatting of the report it is increased from 0 to 1, 2, 3 and 4. When you print, the value keeps increasing, so that it doesn't refer to a valid series any more. This can be fixed by making intIndex a module-level variable and resetting it in the On Page event of the report (this will occur again when the report is printed):

    Private intIndex As Integer

    Private Sub Corpo_Format(Cancel As Integer, FormatCount As Integer)
    Me.Casella15.BackColor = Me.<!t>[Produzione Mazzunno]<!/t>.Form. _
    ChartSpace.Charts(0).SeriesCollection(intIndex).In terior.Color
    intIndex = intIndex + 1
    End Sub

    Private Sub Report_Page()
    intIndex = 0
    End Sub

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

    Re: Rearranging a Query Result Set (2003)

    When I print the report, the Detail_Format event does occur again for each record.

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
  •