Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorted Crosstab (2003 SP1)

    Hello again. I'm trying to sort a crosstab query by the total of the values in a record. But the only way I can get anywhere is by using 2 other queries; 1 to sum the values & the other to bring them together.

    Of course this doesn't work when the crosstab produces fewer columns.

    Help!

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

    Re: Sorted Crosstab (2003 SP1)

    What exactly do you mean by "the total of the values in a record"? Do you mean the row total? You can calculate that in the crosstab query itself, by adding the same field you used for the Value, but this time set the Crosstab option to Row Header.
    You can then create a new query based on the crosstab query, and add * to the query grid (not all individual fields, for they might change!). Also add the row total field, clear its Show check box and specify Ascending or Descending as sort order.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    Thank you Hans, got it now.

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    Oops, the query works great, but the chart give a curious result; the values are doubled.

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

    Re: Sorted Crosstab (2003 SP1)

    You shouldn't include the row total in the row source of the chart control.

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    I've just set that to the query. I don't know how to 'de-select' the field used for sorting.

    NB here's the query:-
    SELECT Query4.*
    FROM Query4
    ORDER BY Query4.SumOfSCRAP_KGS1 DESC;

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

    Re: Sorted Crosstab (2003 SP1)

    I fear you're stuck here. If you enumerate the fields explicitly, you run into problems if the crosstab returns more or fewer fields. If you use *, the row sum will be included. So you may have to do without the special sort order.
    Alternatively, you could build the SQL for the query in code.

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    I wish I could, but that's a bit beyond my capabilities.

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

    Re: Sorted Crosstab (2003 SP1)

    If you wish, we can help with the code. Otherwise, I'd do without the sort order.

  10. #10
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    That's very generous of you, I'd like that. Thank you, I'm always willing to learn more.

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

    Re: Sorted Crosstab (2003 SP1)

    could you post a stripped down version of the database? See <post#=401925>post 401925</post#> for instructions.

  12. #12
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    OK here goes. I've had to strip out lots of data to get the size down, but all the relevant components are there.

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

    Re: Sorted Crosstab (2003 SP1)

    Here is a modified version. I have used DAO code, so in the Visual Basic Editor, I selected Tools | References... and set a reference to the Microsoft DAO 3.6 Object Library.

  14. #14
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    Excellant! Thank you very much. I can read the code & make some sense of it, but I'll need to learn a lot more before I'll be able to do any myself.

    Once again, thanks.

  15. #15
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorted Crosstab (2003 SP1)

    Hans, It looks like I'll be doing several charts from sorted crosstab queries. Could you give me some pointers on the parts of the copied code I'd need to edit.

    For example, the latest chart/query doesn't have a seperate query to do the sorting (yet the chart still doubles the results);

    PARAMETERS [forms]![FrmPassThruSelect]![TxtPress] Text ( 255 ), [forms]![FrmPassThruSelect]![Text20] Text ( 255 );
    TRANSFORM Sum(Tbl_Scrap.SCRAP_KGS) AS SumOfSCRAP_KGS
    SELECT Tbl_Scrap.PRESS, Sum(Tbl_Scrap.SCRAP_KGS) AS SumOfSCRAP_KGS1
    FROM Tbl_Scrap
    WHERE (((Tbl_Scrap.DATE) Like [forms]![FrmPassThruSelect]![Text20]))
    GROUP BY Tbl_Scrap.PRESS
    ORDER BY Tbl_Scrap.PRESS DESC
    PIVOT Tbl_Scrap.OP_CODE;

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
  •