Grouping in 'time-chunks' e.g. '3-Months old'

[Naresh Kumar Saini]

I am new to Access and working on an Access database where I have to a print
report grouped on custom defined time-chunks, e.g.

From a given date -- Today or [End Date] -- Parameter entered by user at
print time:
(1) All dates Less Than Three Months old (27-May-2003 to 26-August-2003)
(2) All dates Between Three Months & Six Months
(3) All dates Between Six Moths & One Year
(4) All dates Between One Yesr & Two Year
(5) All dates More Than Two Year old

Access has in-built support for grouping on months, quarters, years, etc but
I need custom, un-equal groups from a given date. I also need to count, etc.
records falling in each group and I think Domain Aggregate functions
(DCount, etc) can be used for it. I can calculate a date 'Three Months
Before' with DateAdd function. But I can not defince a Section/Group of
required group of time-chunk.

I am a new user to Access.

Naresh
(e-mail address removed)

PS: I created a text box in Page Header of my Report and tried to define its
Control Source property as below (to calculate records falling withing first
time-chunk):

=DCount([Experiment_ID],"Experiment","[OrderDate] <= [End Date] and
[OrderDate] > DateAdd ("m", -3, [End Date])")

where Experiment is table name, OrderDate is date field, [End Date] is user
input (parameter) supplied by user at the time of printing of report.

I built the formula with formula builder or directly type it; but always
when I come out it (no Save command in Access) the text box is still
'unbounded' and the formula is gone. Even if I try to copy and past this
formula in some other text box, it is not saved and still the older content
of text box is there. Is the formula too long or something? Yet, the
following is working OK and giving correct result -- [Begin Date] parameter
is supplied by user:

=DCount([Experiment_ID],"Experiment"," [OrderDate] >= [Begin Date] and
[OrderDate] <= [End Date] ")

I wonder why?

 

[John Vinson]

I am new to Access and working on an Access database where I have to a print
report grouped on custom defined time-chunks, e.g.

From a given date -- Today or [End Date] -- Parameter entered by user at
print time:
(1) All dates Less Than Three Months old (27-May-2003 to 26-August-2003)
(2) All dates Between Three Months & Six Months
(3) All dates Between Six Moths & One Year
(4) All dates Between One Yesr & Two Year
(5) All dates More Than Two Year old

I'd suggest creating a small five-row table with three fields:
TimeChunk, Start, and End, with values


Less Than Three Months old -3 0
Between Three Months & Six Months -6 -3
Between Six Moths & One Year -12 -6
Between One Year & Two Year -24 -12
More Than Two Year old -1000 -24

You could then put a Combo Box on your form, unbound, showing only the
description. You'ld then base your Report on a Query referencing this
form's combo box with a criterion

= DateAdd("m", Forms!YourForm!YourCombo.Column(1), Date())

AND < DateAdd("m", Forms!YourForm!YourCombo.Column(2), Date())

 

[LeAnne]

Have you tried the Partition() function? It's very useful when used in
conjunction with crosstab queries to group data into "bins." Something
like

TRANSFORM First(YourField) AS FirstOfYourField
SELECT YourKeyField, YourDateField, <others>
FROM YourTable
GROUP BY <all fields in SELECT clause>
PIVOT Partition(Int(Month(YourTable.YourDateField)),1,12,3);

Hope this helps,

LeAnne

 

[Naresh Kumar Saini]

Thanks John.

This seems to be a work around. I will try that and hope I would be able to
do it.

In the meantime I still wonder why one of my formula failed to 'bound' to
the text box while another similar formula was working. There do not appear
to be any sysntx error. Perhaps I still be needing this formula for counting
records for a particular chunk.

This one worked:
=DCount([Experiment_ID],"Experiment"," [OrderDate] >= [Begin Date] and
[OrderDate] <= [End Date] ")

And this did not:
=DCount([Experiment_ID],"Experiment","[OrderDate] <= [End Date] and
[OrderDate] > DateAdd ("m", -3, [End Date])")


Thanks again

Naresh
(e-mail address removed)


Anyway, I will try it one-by-one.

I am new to Access and working on an Access database where I have to a print
report grouped on custom defined time-chunks, e.g.

From a given date -- Today or [End Date] -- Parameter entered by user at
print time:
(1) All dates Less Than Three Months old (27-May-2003 to 26-August-2003)
(2) All dates Between Three Months & Six Months
(3) All dates Between Six Moths & One Year
(4) All dates Between One Yesr & Two Year
(5) All dates More Than Two Year old

I'd suggest creating a small five-row table with three fields:
TimeChunk, Start, and End, with values


Less Than Three Months old -3 0
Between Three Months & Six Months -6 -3
Between Six Moths & One Year -12 -6
Between One Year & Two Year -24 -12
More Than Two Year old -1000 -24

You could then put a Combo Box on your form, unbound, showing only the
description. You'ld then base your Report on a Query referencing this
form's combo box with a criterion

= DateAdd("m", Forms!YourForm!YourCombo.Column(1), Date())

AND < DateAdd("m", Forms!YourForm!YourCombo.Column(2), Date())

 

[Naresh Kumar Saini]

Dear John,

I tried to do it the way you suggested but there is some confusion. I
created a TimeChunk tables with three fields, TimeChunk, StartDate, EndDate
and filled in five rows with data you suggested (numeric -3, 0; -6, -3;
etc). Now, is this table has to be related to the main table in some way?

If I add the Combo Box on form 'unbound', what is its use? And, finally, can
a Query refer to values in Form?

I guess you suggested to add a Combo Box (reflecting the table Time Chunk)
to my main data entry form and put in a value from either of five record
(More than 3 months ...) for each record (one-to-many relationship) at the
time of data entry. Then it would be possible to group records on Report.
But I have to feed in a TimeChunk value for each record in main table,
including the ones already exist in the main table. And as date changes, a
record may fall in another Time Chunk, e.g. from > 3 month to 3-6 months
old. May be Update Query can be of some help in this case.

Please clarify a little bit more. Here is something what I need in my
Report:

Group1 (e.g. North)
Less than 3 months
1. Field1 Filed2 ....
2. Field1 Field2 ....
........
Total in 'Less than 3 months' 128
Less than 6 months
.....
Less than 1 year
.....
Group2 (e.g. West)

Thanks,

Naresh
(e-mail address removed)

I am new to Access and working on an Access database where I have to a print
report grouped on custom defined time-chunks, e.g.

From a given date -- Today or [End Date] -- Parameter entered by user at
print time:
(1) All dates Less Than Three Months old (27-May-2003 to 26-August-2003)
(2) All dates Between Three Months & Six Months
(3) All dates Between Six Moths & One Year
(4) All dates Between One Yesr & Two Year
(5) All dates More Than Two Year old

I'd suggest creating a small five-row table with three fields:
TimeChunk, Start, and End, with values


Less Than Three Months old -3 0
Between Three Months & Six Months -6 -3
Between Six Moths & One Year -12 -6
Between One Year & Two Year -24 -12
More Than Two Year old -1000 -24

You could then put a Combo Box on your form, unbound, showing only the
description. You'ld then base your Report on a Query referencing this
form's combo box with a criterion

= DateAdd("m", Forms!YourForm!YourCombo.Column(1), Date())

AND < DateAdd("m", Forms!YourForm!YourCombo.Column(2), Date())

 

[John Vinson]

In the meantime I still wonder why one of my formula failed to 'bound' to
the text box while another similar formula was working. There do not appear
to be any sysntx error. Perhaps I still be needing this formula for counting
records for a particular chunk.

This one worked:
=DCount([Experiment_ID],"Experiment"," [OrderDate] >= [Begin Date] and
[OrderDate] <= [End Date] ")

This can't POSSIBLY have worked. The first argument to DCount should
be a Text String indicating a field. Shouldn't it be

=DCount("[ExperimentID]", etc.

And this did not:
=DCount([Experiment_ID],"Experiment","[OrderDate] <= [End Date] and
[OrderDate] > DateAdd ("m", -3, [End Date])")

The " before the m of dateadd is terminating the quoted string. If
you're going to include " characters within a string delimited by "
characters, you must double up the doublequotes. Try

=DCount("[Experiment_ID]","Experiment","[OrderDate] <= [End Date] and
[OrderDate] > DateAdd (""m"", -3, [End Date])")

or, for protection against misinterpreted or mis-entered dates,

=DCount("[Experiment_ID]","Experiment","[OrderDate] <= #" & CDate([End
Date]) & "# and [OrderDate] > #" & DateAdd ("m", -3, [End Date]) &
"#")

 

[John Vinson]

Dear John,

I tried to do it the way you suggested but there is some confusion. I
created a TimeChunk tables with three fields, TimeChunk, StartDate, EndDate
and filled in five rows with data you suggested (numeric -3, 0; -6, -3;
etc). Now, is this table has to be related to the main table in some way?

No. Just use the criterion as suggested above.

If I add the Combo Box on form 'unbound', what is its use? And, finally, can
a Query refer to values in Form?

Yes, use a criterion of

=Forms!NameOfForm!NameOfControl - *as I suggested above*.

Did you actually try my suggestion?

I guess you suggested to add a Combo Box (reflecting the table Time Chunk)
to my main data entry form and put in a value from either of five record
(More than 3 months ...) for each record (one-to-many relationship) at the
time of data entry.

No. That would be extremely bad design...

Then it would be possible to group records on Report.
But I have to feed in a TimeChunk value for each record in main table,
including the ones already exist in the main table. And as date changes, a
record may fall in another Time Chunk, e.g. from > 3 month to 3-6 months
old. May be Update Query can be of some help in this case.

for this very reason.

Just use the Form Control as criteria, as I suggested. It'll work!

 

[Naresh Kumar Saini]

Dear John,

Yes, I actually created the Table, Form and Query but I could not fugure out
how to proceed as you suggested. The following is still vague to me:

*******

I tried to do it the way you suggested but there is some confusion. I
created a TimeChunk tables with three fields, TimeChunk, StartDate, EndDate
and filled in five rows with data you suggested (numeric -3, 0; -6, -3;
etc). Now, is this table has to be related to the main table in some way?

No. Just use the criterion as suggested above.

If I add the Combo Box on form 'unbound', what is its use? And, finally, can
a Query refer to values in Form?

Yes, use a criterion of
=Forms!NameOfForm!NameOfControl - *as I suggested above*.
Did you actually try my suggestion?
*******

Though I am detrimined to try out what you suggested, in the meantime
another workaround occured to me from what I interpreted to be your
suggestion. It is working (at least 90%) and use only in-built functions of
Access. Please comment if it is a good workaround.

I used 'IF' functions in Excel and in Access it is 'IIF'. I added a
calculated field 'TimeChunk' to my Qurey with (manual line feed for easy
view):
TimeChunk:
IIf([FIR Date]>DateAdd("m",-3,[OrderDate]),"3M",
IIf([OrderDate]>DateAdd("m",-6,[OrderDate]),"6M",
IIf([OrderDate]>DateAdd("m",-12,[End Date]),"1Y",
IIf([FIR Date]>DateAdd("m",-24,[End Date]),"2Y","
<2Y"))))
Now a proper TimeChunk is displayed as text for each record based on [End
Date] and Access can readily group on it. What remain is counting number of
records for each TimeChunk group. Perhaps there is not better way than
'DCount' for it.

To learn Access more intimately I will sure try out the workaround you
suggested. Thanks for your help.

Naresh
(e-mail address removed)

PS: After adjusting and balancing the " and "" the other formula is also
working.