Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Week Sort (Access 2.0)

    Another Access 2.0 Chart problem......

    I am putting together a chart of the amount of quotes done per week.
    In the query, the weeks and years are sorted correctly, but in the form or report they are sorted by first digit. (1,2,21,22,23,24,25,26,27,28,29,3,31,32 etc.).
    In the datasheet for the chart they are out of order. I have reset them but they are not corrected on the form or report. I unchecked the Linked to Source box ( which helped with my last problem) to no avail.

    Any ideas?

    Thanks,
    Mark

  2. #2
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Sort (Access 2.0)

    To follow up....

    I went to the Microsoft Knowledge Base and found an article that may help. (Q141235)
    The article talks about how to change the SQL to change the chart's sorting order.
    Here is the RowSource SQL to the chart report.

    TRANSFORM Sum(qryQuoteHistoryCountperWeek.Quantity) AS Quantity
    SELECT qryQuoteHistoryCountperWeek.Week
    FROM qryQuoteHistoryCountperWeek
    GROUP BY qryQuoteHistoryCountperWeek.Week
    PIVOT qryQuoteHistoryCountperWeek.Year;

    Is there a way, modifying the SQL, to make the week number be in proper order?

    Thanks,
    Mark

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

    Re: Week Sort (Access 2.0)

    You do what the article suggests: add on ORDER BY clause to your SQL. If you're asking *how* to add it, it would be like this:

    GROUP BY qryQuoteHistoryCountperWeek.Week
    ORDER BY qryQuoteHistoryCountperWeek.Week
    PIVOT qryQuoteHistoryCountperWeek.Year;
    Charlotte

  4. #4
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Sort (Access 2.0)

    Thanks for your help.
    However, unless I did it wrong, it did not work.

    I added the line where I should and it didn't make a difference.
    It didn't matter if it was ascending or descending.

    But, in design view it is correct.
    Does that mean anything?

    Thanks,
    Mark

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Sort (Access 2.0)

    Seems that week is a string.
    Try to change it to a number
    GROUP BY Val(qryQuoteHistoryCountperWeek.Week)
    ORDER BY Val(qryQuoteHistoryCountperWeek.Week)
    PIVOT qryQuoteHistoryCountperWeek.Year;

    Hope the function Val exist in Access 2.0, it's to far away to remember.
    Francois

  6. #6
    Star Lounger
    Join Date
    May 2001
    Location
    New Jersey, USA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Week Sort (Access 2.0)

    IT WORKED!!!!!!!!!!!!!!

    Thanks alot !!!!!!

    I just had to do a little minor tweaking and it works the way I want it to.

    I thought that I was doomed!

    <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

    Thanks again,
    Mark

Posting Permissions

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