Grouping dates in special periods

F

FA

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?
 
F

FA

Joseph Meehan said:
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.
Click to expand...

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!
 
V

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.
 
G

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.
 
F

FA

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!
 
K

KARL DEWEY

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.
 
Top