ms access - SUM of difference in values -

ms access - SUM of difference in values -

i need query in ms access difference in value of column 8 , if greater 8.

so if have column of numbers 1-10, want query sum of value's differences 8. result of query below column 3. (9-8)+(10-8)

select sum(([time1]-8)+([time2]-8)+([time3]-8)+([time4]-8)+([time5]-8)+([time6]-8)+([time7]-8)+([time8]-8)+([time9]-8)+([time10]-8)+([time11]-8)+([time12]-8)+([time13]-8)+([time14]-8)+([time15]-8)+([time16]-8)+([time17]-8)+([time18]-8)+([time19]-8)+([time20]-8)+([time21]-8)+([time22]-8)) total tbltimetracking (((month(([day])))=month(now()))) , ([time1]>8 and[time2]>8 and[time3]>8 and[time4]>8 and[time5]>8 and[time6]>8 and[time7]>8 and[time8]>8 and[time9]>8 and[time10]>8 and[time11]>8 and[time12]>8 and[time13]>8 and[time14]>8 and[time15]>8 and[time16]>8 and[time17]>8 and[time18]>8 and[time19]>8 and[time20]>8 and[time21]>8 and[time22]) ;

thanks,

how about:

select sum([value]-8) sumofval table [value]>8

edit re finish alter in original question.

it not clear want

select sum(([time1]-8)+([time2]-8) ... [time1]>8 , time2>8 ...

time1>8 exclude nulls, if not doing, need consider:

nz([time1],0) + ...

edit re comments

something like:

select sum(times) (select iif(time1>8,time1-8,time1) times table union select iif(time2>8,time2-8,time2) times table) b

as b alias: access sql

union / union all: view unified result multiple queries union query

ms-access sum multiple-columns