Weeks in wrong order?

jakobal

Registered User.
Local time
Today, 11:56
Joined
Jan 11, 2008
Messages
14
Hi everyone!

I have two columns. In one of them I have daily dates and in the other I have some numbers. What I'm trying to do is to sum the numbers weekly by entering this in the field of a query:

Week: Format([Date];"yyyy\ ww")

The problem is that when I go to datasheet view, it is sorted wrong.
It looks like this:

2007 1
2007 10
2007 11
2007 12
.
.
.
2007 19
2007 2
2007 21

But I want it like this;

2007 1
2007 2
2007 3
2007 4
.
.
.
2007 9
2007 10
2007 11

and so on...

The other column has the rihght values for each week, but I need it in the right order...

What am I doing wrong? Or how can I fix it?

Thanks in advance
Jake
 
Have you tried adding
Code:
ORDER BY [Date]
to your query?
That should display them in the order you want, but sort them correctly.
 
Order by date seems to give the same result?

Maybe I should explain a little more how I'm doing this...

In my table I have one column with dates and one with testresults. If the test failed it will have the value zero, if it's passed it will have the value 1. For each day I have a lot of these records. In my first query I group them by date(daily) and sum the results having the criteria "1".

In my next query, that is based on the first, I try to sum the results weekly entering this in my first column: Week: Format([Date];"yyyy\ ww")
In the other column I have my results
Finaly I group by Week, and sum the column with results.

That's when I get :

Week Results
2007 1 884
2007 10 447
2007 11 610
2007 12 735
.
.

when I want to have it like this:

Week Results
2007 1 884
2007 2 564
2007 3 910
2007 4 784

since after week 1 comes week 2, not week 10...
 
Formatting the Date only results in a string and it will sort based on STRING values, not date values. Include the date field by itself with no formatting (it doesn't have to be on the report itself) and sort by IT instead of your Week.

Also, if you have a field named DATE you had better change it as that is a reserved word in Access and it will cause you no end of grief at some point if you continue to use it as a field name.

Check here for more reserved words not to use:
http://support.microsoft.com/kb/q286335/
 
Thanks for the help, but it still doesn't work.

When I do that, then I get the results sumed up by day, displaying in my first column which week it is. So since I have results from 5 days/week, I get my weeks devided into 5 results, the daily results.

My final goal is actually to show this in a chart. I tried to sum the results choosing this when building the chart, but the problem then is that I always get two results for each week, so it doesn't completly sum it weekly. Maybe I'm not being clear...

On my X-axis I get the week like this

2007 49
2007 49
2007 50
2007 50
2007 51
and so on...

so each week is divided into two. But it looks like the number of passed results always add up in the first of the two... this means the values on the y-axis are like this..

2007 49 578
2007 49 0
2007 50 743
2007 50 0
2007 51 694
and so on...

so you can imagine what my chart looks like.. up and down up and down...

I don't know, maybe this problem is easier to solve?

Thanks again for the help
 
Maybe I'm not being clear...
True - based on your initial post I didn't know you were talking about a CHART.

Create a query with the Date field as the sort and then use that query in another query that has the two fields you need and it SHOULD be sorted.
 
As far as I can see the only way out of this is to get the leading 0 into the week, not difficult but with the problem that atleast in the only way I know how to do that you cannot group or sort in the same query thus an extra query is required.

Expr1:format([fldDate],"ww")
Expr2:Iif(expr1<=9,"0"&[expr1],[expr1])
Expr3:format([fldDate],"yyyy")&" "&[expr2]

This is done in a Select only query, the summing and grouping is then done in a query that uses this query and you can group on Expr3 or whatever you care to call it.

Brian
 

Users who are viewing this thread

Back
Top Bottom