Assign Dates To One Week Periods And Two Week Periods

[Steve]

I have a table of transaction dates that span multiple years. Query1 is
based on this table and returns only the transaction dates between a start
date and an end date. I need a query based on QUERY1 with two calculated
fields that show which week and which two week period each date falls in.
Weeks and two week periods start on Sunday. The end date is always a
saturday.

For example, the data for the past six weeks would look like:

TransactionDate OneWeek
TwoWeek
4/8/09 4/5/09 to 4/11/09
4/5/09 to 4/18/09
4/15/09 4/12/09 to 4/18/09
4/5/09 to 4/18/09
4/18/09 4/12/09 to 4/18/09
4/5/09 to 4/18/09
4/20/09 4/19/09 to 4/25/09
4/19/09 to 5/2/09
4/24/09 4/19/09 to 4/25/09
4/19/09 to 5/2/09
4/26/09 4/26/09 to 5/2/09
4/19/09 to 5/2/09
5/1/09 4/26/09 to 5/2/09
4/19/09 to 5/2/09
5/7/09 5/3/09 to 5/9/09
5/3/09 to 5/16/09
5/9/09 5/3/09 to 5/9/09
5/3/09 to 5/16/09
5/10/09 5/10/09 to 5/16/09
5/3/09 to 5/16/09
5/15/09 5/10/09 to 5/16/09
5/3/09 to 5/16/09

Can anyone offer suggestions on how to set this up?

Thanks!

Steve

 

[John... Visio MVP]

Contact PCDatasheet at www.PCDataSheet.com. He will help at a reasonable
fee.

John... Visio MVP

 

[Amy-Ann Yablonski]

I have a table of transaction dates that span multiple years. Query1 is
based on this table and returns only the transaction dates between a start
date and an end date. I need a query based on QUERY1 with two calculated
fields that show which week and which two week period each date falls in.
Weeks and two week periods start on Sunday. The end date is always a
saturday.

For example, the data for the past six weeks would look like:

TransactionDate OneWeek TwoWeek
4/8/09 4/5/09 to 4/11/09 4/5/09 to 4/18/09
4/15/09 4/12/09 to 4/18/09 4/5/09 to 4/18/09
4/18/09 4/12/09 to 4/18/09 4/5/09 to 4/18/09
4/20/09 4/19/09 to 4/25/09 4/19/09 to 5/2/09
4/24/09 4/19/09 to 4/25/09 4/19/09 to 5/2/09
4/26/09 4/26/09 to 5/2/09 4/19/09 to 5/2/09
5/1/09 4/26/09 to 5/2/09 4/19/09 to 5/2/09
5/7/09 5/3/09 to 5/9/09 5/3/09 to 5/16/09
5/9/09 5/3/09 to 5/9/09 5/3/09 to 5/16/09
5/10/09 5/10/09 to 5/16/09 5/3/09 to 5/16/09
5/15/09 5/10/09 to 5/16/09 5/3/09 to 5/16/09

Can anyone offer suggestions on how to set this up?

Thanks!

Steve

 

[Keith Wilby]

Can anyone offer suggestions on how to set this up?

Yeh it's a piece o'cake. Cost you though.

 

[kate]

I have a table of transaction dates that span multiple years. Query1 is
based on this table and returns only the transaction dates between a start
date and an end date. I need a query based on QUERY1 with two calculated
fields that show which week and which two week period each date falls in.
Weeks and two week periods start on Sunday. The end date is always a
saturday.

For example, the data for the past six weeks would look like:

TransactionDate OneWeek TwoWeek
4/8/09 4/5/09 to 4/11/09 4/5/09 to 4/18/09
4/15/09 4/12/09 to 4/18/09 4/5/09 to 4/18/09
4/18/09 4/12/09 to 4/18/09 4/5/09 to 4/18/09
4/20/09 4/19/09 to 4/25/09 4/19/09 to 5/2/09
4/24/09 4/19/09 to 4/25/09 4/19/09 to 5/2/09
4/26/09 4/26/09 to 5/2/09 4/19/09 to 5/2/09
5/1/09 4/26/09 to 5/2/09 4/19/09 to 5/2/09
5/7/09 5/3/09 to 5/9/09 5/3/09 to 5/16/09
5/9/09 5/3/09 to 5/9/09 5/3/09 to 5/16/09
5/10/09 5/10/09 to 5/16/09 5/3/09 to 5/16/09
5/15/09 5/10/09 to 5/16/09 5/3/09 to 5/16/09

Can anyone offer suggestions on how to set this up?

Thanks!

Steve

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can find the Sun/Sat dates like this:

One week:

Sunday: DateAdd("d",1-Weekday(TransactionDate),TransactionDate)
Saturday: DateAdd("d",7-Weekday(TransactionDate),TransactionDate)

Two week Saturday:

DateAdd("d",14-Weekday(TransactionDate),TransactionDate)

For two week Sunday use the same formula as the one week Sunday.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSoO+WIechKqOuFEgEQJbwgCg8+SQBRDMkzPDiaBXlbqJWxyaFRwAnR5T
CTpjt1GYfgR//wdiQKxsuHmX
=WBDL
-----END PGP SIGNATURE-----