Assign Dates To A specific Period

[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

 

[Bob Quintal]

The temerity in your new address is galling.
Besides, it should be help_required@ not available.

To answer your request for help, build a table with [startdate] and
[enddate] fields, to hold the various two week periods, then throw
that table into your second query. Add the two fields from the table.
Join on [TransactionDate] to [startdate]. Change to SQL view on the
query and edit the join from
[queryname].[TransactionDate] = [tablename].[Startdate]
to
[queryname].[TransactionDate] Between [tablename].[Startdate]
AND [tablename].[EnddDte] then save the query.

Q

 

[Beetle]

I'm sure Steve will be more than willing to pay you a
"reasonable fee" for the help you have provided him Bob.

After all, he's probably doing this for someone else and charging
them for his "expertise".

--
_________

Sean Bailey

The temerity in your new address is galling.
Besides, it should be help_required@ not available.

To answer your request for help, build a table with [startdate] and
[enddate] fields, to hold the various two week periods, then throw
that table into your second query. Add the two fields from the table.
Join on [TransactionDate] to [startdate]. Change to SQL view on the
query and edit the join from
[queryname].[TransactionDate] = [tablename].[Startdate]
to
[queryname].[TransactionDate] Between [tablename].[Startdate]
AND [tablename].[EnddDte] then save the query.

Q

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]

The temerity in your new address is galling.
Besides, it should be help_required@ not available.

To answer your request for help, build a table with [startdate] and
[enddate] fields, to hold the various two week periods, then throw
that table into your second query. Add the two fields from the table.
Join on [TransactionDate] to [startdate]. Change to SQL view on the
query and edit the join from
[queryname].[TransactionDate] = [tablename].[Startdate]
to
[queryname].[TransactionDate] Between [tablename].[Startdate]
AND [tablename].[EnddDte] then save the query.

Q

The new address has been around for a while. It is just steve being steve.
This is also the third time he has posted the same question. As pointed out
in one of the other threads, he needs to find out from whoever was foolish
enough to hire him what the definition of the two week period is. The query
is easy to do, but the request is still vague.

John... Visio MVP