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
(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
__._,_.___
Posted by: John Viescas <johnv@msn.com>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
New Polling Options - Get Out the Vote!
You can now add a new option in an existing poll and vote directly from email
Yahoo Groups
Control your view and sort preferences per Yahoo Group
You can now control your default Sort & View Preferences for Conversations, Photos and Files in the membership settings page.
.
__,_._,___
Tidak ada komentar:
Posting Komentar