Update query: To roll forward a date by 52 weeks using a look-up f

[Guest]

I created a table named WeekId which contains the following fields:
WeekID (PK) (ie. weekID = 28)
BegDate (Date) Format: Short date (ie. 07/05/04) (weekID = 28)
EndDate (Date) Format: Short date (ie. 07/11/04) (weekID = 28)

I also created a table called JobsinProgAssigned, which contains:
- a look-up field called WeekID to the above noted table
- a field called DateIn which is an actual date field (ie. July 8, 2004)


I want to create an Update query which will:
- take the date in the DateIn.JobsinProgAssigned (ie. July 8, 2004)
- add 52 weeks to it (ie. July 1, 2005)
- find the week range it fits into in WeekID.WeekID (ie. 06/27/05 - 07/03/05 - which is weekID #79 on my table)
- update the WeekID.JobsinProgress field to the WeekID.WeekID found in the previous steps (ie. 79)

In essence, I am receiving a file on a particular date, July 8, 2004. When I am done with this file, I want to roll it forward to the next year(2005) so that I can create a schedule for 2005. This schedule will place all files exactly 52 weeks in advance of when I received them this year -2004 (ie. for this particular file the week would be: June 27 - July 3, 2005).

If you can help me out that would be spectacular!

Thanks

 

[Michael Keating]

Hi,

I think you've made this problem much worse for yourself than it needs to
be.

If you look up the DATEPART function in the help files, you'll find that
specifying "ww" as the interval setting will return a week number, for a
given date.

In your problem, since you store the date the file was received, then you
can use that function to find it's week number, so when you want to find
jobs at some time in the future, you simply look for jobs who's week number
matches the week number for the "current" date.

i.e. SELECT * FROM tblJobs WHERE (DATEPART("ww",[DateReceived]) =
DATEPART("ww",Now())

With a little more work you could also give jobs a "periodicity" in weeks,
such that you could have jobs that run every 4, 8, 13, 26, or whatever,
weeks to give yourself some extra flixibility.

There are quite a few functions that work with dates to make this sort of
problem easier to handle.

Also July 8, 2004 plus 52 weeks (or 1 year) is definitely July 8, 2005 <G>

HTH

MFK.

I created a table named WeekId which contains the following fields:
WeekID (PK) (ie. weekID = 28)
BegDate (Date) Format: Short date (ie. 07/05/04) (weekID = 28)
EndDate (Date) Format: Short date (ie. 07/11/04) (weekID = 28)

I also created a table called JobsinProgAssigned, which contains:
- a look-up field called WeekID to the above noted table
- a field called DateIn which is an actual date field (ie. July 8, 2004)


I want to create an Update query which will:
- take the date in the DateIn.JobsinProgAssigned (ie. July 8, 2004)
- add 52 weeks to it (ie. July 1, 2005)
- find the week range it fits into in WeekID.WeekID (ie. 06/27/05 -

07/03/05 - which is weekID #79 on my table)

- update the WeekID.JobsinProgress field to the WeekID.WeekID found in the previous steps (ie. 79)

In essence, I am receiving a file on a particular date, July 8, 2004. When

I am done with this file, I want to roll it forward to the next year(2005)
so that I can create a schedule for 2005. This schedule will place all files
exactly 52 weeks in advance of when I received them this year -2004 (ie. for
this particular file the week would be: June 27 - July 3, 2005).