Determine if Trend is Up or Down

[LadyDi]

I have a query that displays the average call rate on various pieces of equipment over the last 12 months. I was wondering if there is a way to have Access look at this query and determine if the call rates are trending up or down over that time frame. Kind of like a trend line would do on a chart in Excel, only I want this to show me the word Up or Down in my results. Is this possible?

Here is the query I am using:

TRANSFORM FormatNumber(Avg([All Call Rate]),2) AS AvgCallRate
SELECT List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
FROM List_With_TNC_tbl
GROUP BY List_With_TNC_tbl.Device, List_With_TNC_tbl.Model, List_With_TNC_tbl.[Item Num]
PIVOT Format([Month Start Date],"mmm") In ("Jan","Feb","Mar","Apr","May", "Jun", "Jul","Aug","Sep", "Oct", "Nov", "Dec");

Any help you can provide would be greatly appreciated.

 

[Guus2005]

Yes you can!
But you have to prepare the query before you turn it into a crosstab query.
You have to add the data to which you want to compare the result over the last 12 months to the query and determine if the trend is up or down.
After that turn it into a crosstab query.

HTH

 

[LadyDi]

What do I need to add to the query to determine the up or down trend? I have twelve numbers that I want to know if they are trending up or down.

 

[Guus2005]

At this time it is better when you provide a sample database.
It is easier to explain that way.