Re: This Month vs Last Month
- From: "Duane Hookom" <duanehookom@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 15 Dec 2005 20:43:32 -0600
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" <RTimberlake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ECFD91DA-8FE2-4845-86FB-F7F00634CBCE@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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.
>>
>> --
>> Duane Hookom
>> MS Access MVP
>> --
>>
>> "RTimberlake" <RTimberlake@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:21600572-0A54-400F-A36A-C9E85597360A@xxxxxxxxxxxxxxxx
>> >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?
>>
>>
>>
.
- References:
- Re: This Month vs Last Month
- From: Duane Hookom
- Re: This Month vs Last Month
- Prev by Date: Re: Report - TextBox - text vertically middle ?
- Next by Date: Report fields cut off on HP 4050N; OK on HP LJ III
- Previous by thread: Re: This Month vs Last Month
- Next by thread: Re: Subreport repeats as many times as there are detail records
- Index(es):
Relevant Pages
|
