Is it possible to write your own total functions for use in query (e.g. max, sum etc. (1 Viewer)

[whitespace]

Hello all, basically we have had this problem a number of times before, we need to be able to write our own custom function to total queries as in the ones available in query designer (sum, min , max etc.). Basically we need to group a number of years together and for these years calculate trends in the returned data. so we have a table for example:

Year Num1 Num2
2003 25 26
2004 28 24
2005 29 22

And would like to return

Num1 Num2
Up Down

(This would return an upward trend in Num1 and downward trend in Num2). I appreciate that we could create another query with a column for each year but it would be so much nicer to be able to write our own function to be used in the query designer (or SQL whatever) to do this (as we have a lot of Nums to calculate). Is this possible?

(We are happy using VBA etc.)

Many thanks for any help.

 

[ColinEssex]

Is there a column for each year? If so how many columns are there?

Col

 

[whitespace]

Thanks for your reply.

Not currently, there is 1 column for the year. There are say 3 rows for each school (with 3 different years in). We want to be able to turn this into 1 row per school with a trend of the 3 years data for each num/field. At the moment the data has 1 column called year, with multiple years as rows. Does that make sense?

 

[ColinEssex]

How many num/fields does each year have?


Col

 

[whitespace]

An example of the actual raw data is attached, basically we want to produce 1 row for these 5 rows where the blue highlighted columns are totalled by showing trends, e.g. Up trend or Down etc. (some fields we will want to create average as well but that is straghtforward obviously). Many thanks for any help

 

[whitespace]

I've solevd this now by pulling in 3 sperate versions of the same query, each looking at a different date and all linked on school number. However I am still interested in knowing if you can produce your own total functions as we have had this before when wanting to create statistical functions on sets of data.

Does anyone know if it is possible to create your own total functions, or is that the limitation with jet-sql??

Many thanks