Sum column values based on Column value (1 Viewer)

[cope]

Hello all - Not sure if this is the correct category but here goes.

I'm looking to do some calculations using numbers in tables. I understand this can be done very simply in Excel but I'm determined to produce the intended results using just Access 2007 - I'm convinced it is possible, I'm just missing that spark to kickstart the process.

The table in question is set up like a spreadsheet (bear with me) - with the field linking this table to the staff table being the staffID - autonumber.

The column heads read
Week commencing
Week ending
M
T
W
Th
F
Sa
Su
Reason
Information

The important aspect is the reason column which can be one of four options. The week commencing and ending are input manually, and the days of the week have been formatted to numbers, which indicates how many hours the given staff member has taken in that week relating to the reason why they took those hours.

I have a select query which runs when clicking a button and allows me to view the information regarding the staff member shown on the form and the hours they have taken - in datasheet view, which looks great.

My initial task is to create a calculation - preferrably using a text box in a form - which reads if Reason is "Annual leave" sum columns M,T,W,Th,F,Sa,Su - for each record this is true.

Could somebody please confirm whether this is indeed possible, or I am forcing this upon a program which is in no way going to give me what i require. I have tried numerous IIf and Dsum formula without success.

Thanks,

Cope.

 

[Guus2005]

select M + T + W + Th + F + Sa + Su as WeekdaySum from Staff where Reason = 'Annual leave'

or when you expect one of the values might be null:

select Nz(M,0) + Nz(T,0) + Nz(W,0) + Nz(Th,0) + Nz(F,0) + Nz(Sa,0) + Nz(Su,0) as WeekdaySum from Staff where Reason = 'Annual leave'

or if you want a total sum of all weekdays thoughout your table:

select Sum(Nz(M,0) + Nz(T,0) + Nz(W,0) + Nz(Th,0) + Nz(F,0) + Nz(Sa,0) + Nz(Su,0)) as TotalWeekdaySum from Staff where Reason = 'Annual leave'

HTH