This Month vs Last Month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report in which I would like to compare last months figures with
this months figures. I do not want to see the previous months figure. I only
need to know if the figure increased, decreased, or stayed the same. Any
suggestions?
 
Do you have a table structure and sample records you would like to share?
You have stated where you want to go but we don't know where you are or what
you have for resources.
 
Sorry.
My report is pulling from a query that combines a table and two other
queries.
We are trying to create a database that will be used for monthly evaluations
on the boys in our program. The report is currently being pulled for the
current month for each boy in our program. Since we are on a rolling
admissions basis the actual month number is calculated by subtracting the
number of months between a boys arrival and the current month. So, in any
given report we may have a boy in month one or a boy in month 16. However,
the report is being pulled for the current month.
There are several areas that we are measuring: Obedience, Physical, Work
Ethic, etc. For each area we need to know if a boys score increased,
decreased, or stayed the same from last month to this month. I had planned on
using arrows to display the relationship between the scores. The peice that I
am having trouble with is the calculations. I can't figure out how to get
last months score and this months score on the same report.

Report: [Evaluation]
Feilds: [EvalDate], [Name], [MonthsInProgram], [Obedience], [Physical],
[WorkEthic]

Does this clear things up, or have I just muddied the waters even more?
 
I would probably normalize the table first:
SELECT DateDiff("m", [EvalDate], Date()) as MthNum,
[Name], [Obedience] as Score, "Obedience" as Area
FROM tblNoNameGiven
UNION ALL
SELECT DateDiff("m", [EvalDate], Date()) as MthNum,
[Name], [Physical], "Physical"
FROM tblNoNameGiven
UNION ALL
SELECT DateDiff("m", [EvalDate], Date()) as MthNum,
[Name], [WorkEthic], "WorkEthic"
FROM tblNoNameGiven;

Then create a crosstab query based on the union query with [Name] (should be
a primary key field, Name is a poor choice for a field name) as the Row
Heading, Area as another Row Heading, "Mth" & MthNum as the Column Heading,
and Average of Score as the Value. Set the Column Headings property to:
Column Headings: "Mth0", "Mth1"
This will create a query with columns for Name, Area, Mth0, Mth1. Mth0 will
be the current month score and Mth1 will be last month.
--
Duane Hookom
MS Access MVP


RTimberlake said:
Sorry.
My report is pulling from a query that combines a table and two other
queries.
We are trying to create a database that will be used for monthly
evaluations
on the boys in our program. The report is currently being pulled for the
current month for each boy in our program. Since we are on a rolling
admissions basis the actual month number is calculated by subtracting the
number of months between a boys arrival and the current month. So, in any
given report we may have a boy in month one or a boy in month 16. However,
the report is being pulled for the current month.
There are several areas that we are measuring: Obedience, Physical, Work
Ethic, etc. For each area we need to know if a boys score increased,
decreased, or stayed the same from last month to this month. I had planned
on
using arrows to display the relationship between the scores. The peice
that I
am having trouble with is the calculations. I can't figure out how to get
last months score and this months score on the same report.

Report: [Evaluation]
Feilds: [EvalDate], [Name], [MonthsInProgram], [Obedience], [Physical],
[WorkEthic]

Does this clear things up, or have I just muddied the waters even more?


Duane Hookom said:
Do you have a table structure and sample records you would like to share?
You have stated where you want to go but we don't know where you are or
what
you have for resources.
Click to expand...
Click to expand...
 
Back
Top