Grouping dates in special periods

[Guest]

I need to define a group of dates as different periods. An example of the
table below:
Start End Cycle
1/11/2005 1/21/2005 0
2/1/2005 2/11/2005 1
2/14/2005 2/28/2005 2
3/2/2005 3/15/2005 3
3/16/2005 4/1/2005 4
4/6/2005 4/20/2005 5

I though about creating a table with every day and define each one.

What do you recommend?

 

[Joseph Meehan]

I need to define a group of dates as different periods. An example of
the table below:
Start End Cycle
1/11/2005 1/21/2005 0
2/1/2005 2/11/2005 1
2/14/2005 2/28/2005 2
3/2/2005 3/15/2005 3
3/16/2005 4/1/2005 4
4/6/2005 4/20/2005 5

I though about creating a table with every day and define each one.

What do you recommend?

More information. What determines the group? I take it that it changes
every day. Is there a formula based on today's date that you can write out
for us? How about history. Will you ever have any reason to needed what
the groups were three weeks ago? I am sure I missed a few, but that might
be a good start.

 

[Guest]

More information. What determines the group? I take it that it changes
every day. Is there a formula based on today's date that you can write out
for us? How about history. Will you ever have any reason to needed what
the groups were three weeks ago? I am sure I missed a few, but that might
be a good start.

The groups (or cycles) are defined as needed at the beginning of the year.
I have a detailed table that describe sales calls by day, # visits, sample
deliveries, activities, etc.
I want to have totals by cycle.
Thank you very much!

 

[Van T. Dinh]

It sounds to me the Table you posted should be suitable. The only thing is
that there are gaps, (i.e. there are date ranges that are not assigned in
cycles) in the posted Table.

 

[Graham Mandeno]

Create a table of cycles with three fields: CycleNumber, CycleStart and
CycleEnd. Populate it with values like the ones you listed below.

Now, the CycleNumber for a particular sales call date can be determined by
adding this table unjoined to a query of sales calls. As criteria in the
SalesCallDate column, use this expression:
Between [CycleStart] and [CycleEnd]

Add the CycleNumber also to your query and it will show the cycle number for
that date.

 

[Guest]

The gaps are meetings, weekends and other dates where we should not expect
any sales activity (no data either).

I want to create a query where each date is assign to one of the cycles in
the table below. How can I do that?

Thank you!

 

[Van T. Dinh]

See Graham's reply ...

 

[Guest]

Your "detailed table" needs to have the sales calls by date - as in a field
that is datatype DateTime.

Build a query with your "detailed table" and the "cycle" table. Add a
column in the query design view like --
Cycle: Iif([DetailedTable].[SalesDate] Is Between [CycleTable].[Start] and
[CycleTable].[End], [CycleTable].[Cycle], "Out of cycle")

Substitute the actual names of the tables.

 

[Joseph Meehan]

The gaps are meetings, weekends and other dates where we should not
expect any sales activity (no data either).

That famous last word "should." You will and I suggest that you build
in some sort of default handling of those gaps and adjustments to scheduled
dates.