percentages by date

[Chris Holland]

Thanks to Duane, I have recently solved a percentage
problem. I am now able to break down percentages for
today, this week, and this month. My question is how do
I obtain the percentages for an arbitrary number of
days. For instance, if the date is August 2nd, I would
rather see information for the past 30 days rather than
for the month of August. How do I view the information
for an arbitrary number of days? Is it possible to use a
between statement? I have copied the old replies and put
them at the end of this new post so everyone can learn
the situation. Thanks again!

Chris Holland




First create a crosstab query:
== qxtbEventsByDateCode ========
TRANSFORM Count(tblEvents.EventID) AS CountOfEventID
SELECT tblEvents.Date, Count(tblEvents.EventID) AS
AllEvents
FROM tblEvents
GROUP BY tblEvents.Date
PIVOT "Event" & [EventCode];

Then create a report based on this query. The only
section that you need is
the report footer. The detail section can be 0 height.
For the today
percentages, add a text box with the following Control
Source property:
Today event1 (substitute Event2 and Event3 to get other
text boxes)
=Sum([Event1]*Abs([Date]=Date()))/ Sum([AllEvents]*Abs
([Date]=Date()))

To get This Week for Event1:
=Sum([Event1]*Abs(Format([Date],"yyyyww") = Format(Date
(),"yyyyww")))
/Sum([AllEvents]*Abs(Format([Date],"yyyyww") = Format(Date
(),"yyyyww")))

To get This Month for Event1:
=Sum([Event1]*Abs(Format([Date],"yyyymm") = Format(Date
(),"yyyymm")))
/Sum([AllEvents]*Abs(Format([Date],"yyyymm") = Format(Date
(),"yyyymm")))

 

[Duane Hookom]

Each of the expressions contains a subexpression that evaluates to either
true/-1 or false/0. For instance
[Date]=Date()
and
Format([Date],"yyyyww") = Format(Date(),"yyyyww")
You need to create a true/false expression that determines if [Date] is
between your arbitrary dates.
[Date] Between Forms!frmA!txtStartDate and Forms!frmA!txtStartDate + 30
Try this...

--
Duane Hookom
MS Access MVP

Thanks to Duane, I have recently solved a percentage
problem. I am now able to break down percentages for
today, this week, and this month. My question is how do
I obtain the percentages for an arbitrary number of
days. For instance, if the date is August 2nd, I would
rather see information for the past 30 days rather than
for the month of August. How do I view the information
for an arbitrary number of days? Is it possible to use a
between statement? I have copied the old replies and put
them at the end of this new post so everyone can learn
the situation. Thanks again!

Chris Holland




First create a crosstab query:
== qxtbEventsByDateCode ========
TRANSFORM Count(tblEvents.EventID) AS CountOfEventID
SELECT tblEvents.Date, Count(tblEvents.EventID) AS
AllEvents
FROM tblEvents
GROUP BY tblEvents.Date
PIVOT "Event" & [EventCode];

Then create a report based on this query. The only
section that you need is
the report footer. The detail section can be 0 height.
For the today
percentages, add a text box with the following Control
Source property:
Today event1 (substitute Event2 and Event3 to get other
text boxes)
=Sum([Event1]*Abs([Date]=Date()))/ Sum([AllEvents]*Abs
([Date]=Date()))

To get This Week for Event1:
=Sum([Event1]*Abs(Format([Date],"yyyyww") = Format(Date
(),"yyyyww")))
/Sum([AllEvents]*Abs(Format([Date],"yyyyww") = Format(Date
(),"yyyyww")))

To get This Month for Event1:
=Sum([Event1]*Abs(Format([Date],"yyyymm") = Format(Date
(),"yyyymm")))
/Sum([AllEvents]*Abs(Format([Date],"yyyymm") = Format(Date
(),"yyyymm")))

--
Duane Hookom
MS Access MVP

I am basically tracking events that occur and entering
them as they happen. There are 3 events that I track and
they are numbered 1, 2, 3. They appear in the EventCode
field. When the event occurs, I record the date and the
EventCode. Here's a snippet of my table:

EventID Date EventCode
1 7/3/03 1
2 7/3/03 1
3 7/4/03 3
4 7/4/03 2

I would love to have a report that states the percentage
that each event happens broken down by month, then by
week, then by day - all at one glance. The sample in the
prior post is a main goal. Thanks!