Week Sort (Access 2.0)

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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;

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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!

Thanks again,
Mark

Reply | Quote