pivot in query, need first 12 results sumed?

[dtinsley]

ok, i am using pivot in a query. this returns 24 values, one for each hour in the day. i need to sum the values of the first 12 items returned in the query. i need them in the same query. how can this be done? the column is names from 1 through 24, 1 for each hour..

 

[Timtropolis]

Not sure if you can do this within the confines of the pivot query , but you can use the "top values" property of the query and then do a sum. I've had problems in the past with aggregate functions in a Pivot query, so you may want to use the Pivot query as a source and then a seperate query to do the aggregate sum.

HTH
Tim

 

[dtinsley]

yea, i suppose a seperate query would be the best way... wanted it all in one query, but i can use two...

 

[namliam]

Assuming you want 1-12 summed you can do an IIf

AM: sum(Iif(yourtime<=12,yourvalue,0))
PM: sum(Iif(yourtime>12,yourvalue,0))

Your column headers however will not be the way you want them I think....
But principaly what you want to do is possible....