give me the previous 10 records

[flaviooooo]

Hi,

I have a queryproblem. I have a table containing waste-data, sorted by week. The week is in the following format:
3703 -> week 37 of year 2003
2002 -> week 20 of year 2002
...
You get the drift.

Now I have a form where a user types a weeknumber, for example 3003.
I'd have to make a query that gives met the data of the previous 10 weeks (as well as the asked week):
3003
2903
2803
...
2003

Is this possible?
Thanks

 

[AccessAce]

SELECT *
FROM tablename
WHERE week>Me.UserWeekNum-1000 AND week<Me.UserWeekNum

Something like this will work. The idea is that 1 week ago is thisweek-100. So if this week is 3003, last week was 2903, and ten weeks agos was 2003, or 1000 less than this week.

Does this help?

 

[flaviooooo]

Yes, it helps

But the problem would be with the first 10 weeks of a year.

For example the 3th week of a year -> 0303

I'd probably have to capture this in code

 

[cmmrfrds]

Will a Top query work?

Select Top 11 from table
where weekofyear = &quot;0303&quot;
order by weekofyear desc

 

[flaviooooo]

What is a Top Query?

 

[GoDawgs]

Just gives you the top 11 records for that query (first 11). That's not going to work in this case since you can't limit the weekofyear to 0303. This is a tough one...I'll get back to you if I figure something out.

Kevin

 

[AccessAce]

(inputweek-(cint(inputweek/100)*100))+((52-(10+cint(inputweek/100)))*100)

The ()'s may be off, but this should work if inputweek <1199, assuming your always using 52 week years, and that you have another routine to get the first few weeks of the year. This can get you values at the end of the year, but the real problem is that you used the WWYY format. If your dates were YYWW, it would be a whole lot easier.

Using the CInt() function, in combination with / and *, you can take a part a number to change the one you want, but to really make it easy, find a format, like YYWW, that will make your dates sequential. Otherwise, your looking at doing a lot of math and running slow queries.

Dates in order WWYY
102
103
104
5202
5203
5204

If you do a top ten, you will always get either the last weeks of all your years, or the first weeks of all your years.

 

[flaviooooo]

How can it be done easier with YYWW?

Maybe I can twist it around in the table

 

[cmmrfrds]

Work with the year first and then the week by isolating each part with functions left and mid.

Dim curryear as string, pastyear as string
dim aweek string
curryear = &quot;03&quot;
pastyear = &quot;02&quot;
aweek = &quot;03&quot;
'- ending point &quot;0303&quot;

Select Top 11 weekyear from table
where (left(weekyear,2) <= aweek and
mid(weekyear,2,2) = curryear)) or
mid(weekyear,2,2) = pastyear )
order by mid(weekyear,2,2) desc, left(weekyear,2) desc