Report showing current total, previous total and average total?

  • Thread starter Thread starter Richard Black
  • Start date Start date
R

Richard Black

Hi,

I would like to be able to design a report that shows the total monthly
figure, the prvious months total and an average months total. Is this
possible?

I have some experience with VBA, but can't get my head around what data I
would actually need...

Thanks,

Richard
 
Yes, this is possible. You haven't specified if you want to also show
details or if you are grouping. It would help if you provided some sample
records as well as desired output.
 
Hi,

My desired data is basically a count of occurences of things in different
locations. The table would look like:

[date], [thing], [place]

and the outputted data:

[group by place]

[group by thing]

[thing], [count of thing for this month], [count of thing for
previous month], [average monthly count of thing for
previous 12 months]

I have made a start by producing a query that finds all the [things] for a
given month and year (taken from a combo box) and am working on another to
find the [things] for the previous month. I then intend to produce a third
query that will bring together both sets of data - is this a sensible way
forward?

Thanks,

Richard
 
I would create a query that Groups By Place and Thing and uses expressions
to calculate counts.
PrevMthCount: Sum( Abs( Format(Date(),"yyyymm") = Format(DateAdd("m",1,
[Date]),"yyyymm")))
You could create other columns using similar expressions that Sum the
absolute value of a true or false expression.

--
Duane Hookom
MS Access MVP


Richard Black said:
Hi,

My desired data is basically a count of occurences of things in different
locations. The table would look like:

[date], [thing], [place]

and the outputted data:

[group by place]

[group by thing]

[thing], [count of thing for this month], [count of thing for
previous month], [average monthly count of thing for
previous 12 months]

I have made a start by producing a query that finds all the [things] for a
given month and year (taken from a combo box) and am working on another to
find the [things] for the previous month. I then intend to produce a third
query that will bring together both sets of data - is this a sensible way
forward?

Thanks,

Richard


Duane Hookom said:
Yes, this is possible. You haven't specified if you want to also show
details or if you are grouping. It would help if you provided some sample
records as well as desired output.
Click to expand...
data
I
Click to expand...
Click to expand...
 
Back
Top