Access - is there an easy way to calculate

things like min, max, count, average across different fields in access. e.g. I have 6 fields say called quantity1, quantity2, quantity3 and so on. They are number fields but one record might have all six fields with values in whilst another might only have values in four of these fields. I want to be able to say what the smallest number in them is and the largest and count the ones with values in them so that I can then get an average across them. I have no problem over one field in many records but don't know how to do it over many fields in one record.

geek_g14us, Aug 16, 9:14 pm

If you UNPIVOT the quantities from columns into rows first you can do this with regular SQL aggregate functions, see http://stackoverflow.com/questions/7255423/how-to-simulate-unpivot-in-access-2010 for an UNPIVOT example.

Otherwise you could use combinations of functions like
min: min(quantity1, min(quantity2, .))
count: iif(isnull(quantity1),0,1) + iif(isnull(quantity2),0,1) +.

geek_drsr, Aug 16, 10:53 pm

Many thanks drsr. I've had a quick look at the UNPIVOT but think that might be a bit beyond me, although I'll go back and look again later. The second option, although a right hassle, 'cos actually will have 30 fields to work with not just six, will have to do for me. I had been fiddling around with those sort of expressions but hadn't realised I'd have to add the function each time. I was hoping to just do something like min(qnatity1, quantity2, . more like as if it was an array of fields. Oh well, at least it only has to be written out the once so best get to and make sure I have all the brackets in place!

geek_g14us, Aug 17, 10:12 am

For that many fields it would be a bit gross either way. You could look at http://www.access-programmers.co.uk/forums/showthread.php!t=184618 which should you how to make a function that will get the max of an array of fields, and you could create versions of it that do min and count non-nulls.

geek_drsr, Aug 17, 10:24 am

OK will go visiting there, thanks

geek_g14us, Aug 17, 10:57 am

Thanks for trying to help but I can't get anything there to work for me. Probably a bit beyond me! Even if I do it the long way as in your post 2 min: min(quantity1, min(quantity2, .))
I must be doing something wrong because it doesn't give me the lowest figure in those 3 fields.

geek_g14us, Aug 17, 2:09 pm

It should work OK, in Access 2010 go to the Create tab and click on Module, paste the code into the new Module, then in your query add a calculated column with the expression Minimum([field1], [field2], [field3]).

geek_drsr, Aug 17, 6:23 pm

Ok but think I have got myself out of my depth here. I thought it would be simple as but seems not. Never mind I may have to resort to Excel - bugs!

geek_g14us, Aug 17, 6:59 pm