Help With Query

[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]

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

Do you actually understand what the original requestor wants?

From what you have provided, there is insufficient information to proceed.
1) You do not specify what Query1 is.
2) How are the two week periods defined? (How do we know if is the first or
second week of a two week period?)
3) Do we get a cut for helping you solve this one?

John... Visio MVP

 

[John Spencer]

Well, you could use a little date arithmetic to calculate each period

SELECT TransActionDate
, DateAdd("D",1-Weekday(TransActionDate),TransactionDate) as Start
, DateAdd("D",7-Weekday(TranactionDate),TransactionDate) as End7
, DateAdd("D",14-Weekday(TransactionDate),TransactionDate) as End14
FROM SomeTable

The 14 day period will have to be adjusted based on how you determine
your two-week period start dates.

A simpler solution, might be to build a table of one week and two week
periods and use that in a join to determine the periods.

Good luck.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================