ACCESS FEED: R: [MS_AccessPros] query record range

Selasa, 22 Juli 2014

R: [MS_AccessPros] query record range

Perfect, is working

Thank you John

Da: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Inviato: lunedì 21 luglio 2014 19.28
A: MS_Access_Professionals@yahoogroups.com
Oggetto: Re: [MS_AccessPros] query record range

Valentino-

The real trick to this is figuring out records that qualify when you are near the end of one year or the beginning of the next. It would be fairly easy if you had Year / Month / Day instead of Year / Week number. You can get an approximate relative week number by using Year * 52 plus week number, which should be good enough for comparison purposes.

SELECT *

FROM Table1

WHERE ((YearValue * 52) + WeekValue) BETWEEN

((Table1.Year1 * 52) + Table1.Week1) AND

((Table1.Year2 * 52)

ORDER BY Table1.Year1, Table1.Week1;

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 21, 2014, at 12:13 PM, valentino.avvisati@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Good morning

I have a question hoping somebody can help.

(in the past I make a really long mail, that do not get answer (was really too long) , let’ see if I just insert the key point I’m luckiest

I have table 1 that represent the stock of plants, with the following fields:

Variety, week1, year 1, year2, week2, quantity

Inside this table there are some records of plants species with the period can be used, If the stock period is 4 week I have:

Variety1 xxxx from week 2-2014 to week 6 – 2014 quantity 1000

Variety1 xxxx from week 3-2014 to week 7-2014 qantity 1000

Variety1 xxxx from week 4-2014 to week 8-2014 quantity 1000

This mean that In week 2 my availability of variety 1 is 1000

In week 3 my availability is 2000

In week 4, 3000

In week 5, 3000

In week 6, 3000

In week 7 , 2000

And so on

If I have one order in week 3 -2014 I can use the record 1 or 2 but not the record 3.

Is there a query I can do to select only the record where my week is inside the range, ordering the result for the record that have week 1-year 1 smaller?

The goal is take first the oldest plant still valid and after the other one.

Thank you