# SolvedSUM in a query (1 Viewer)

#### mansied

##### Member
Hello
I need a help in sum of some fields
I

I want to sum (p and s and un fileds) id the month is 8 and add in O field?
when i write id the query ,i have a lot of errors
Operation_SUM : IIf ( Month([MONTH)]) ="8",([PLANNED]+[Scheduled]+[Unscheduled]),0)
Could someone help to do this query?
or how can i have a filed of Sum of [PLANNED]+[Scheduled]+[Unscheduled] ??
Thank you

#### MajP

##### You've got your good things, and you've got mine.
The month function returns a number not a letter so no "".
if ( Month([MONTH)]) =8,([PLANNED]+[Scheduled]+[Unscheduled]),0)
Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

#### plog

##### Banishment Pending
You've set your database up like a spreadsheet and should fix it. You have data stored in field names. Instead that data should go into a [Type] field.

Instead of your 8 fields with 6 of them named after the type (OPERATIONAL, PLANNED, SCHEDULED...) you simply need a table with 4 fields:

tblNameHere
nh_ID, autonumber, primary key
nh_Date, date, replaces the existing MONTH field
nh_Type, string, will hold the type currently used as the name in those 6 fields
nh_Value, number, this will hold the number currently in all those type fields

That's it, Now when you have a value for each type you add 6 records into tblNameHere. That's how databases are to be set up--to accomodate data vertically (with more rows) and not horizontally (with more columns)

#### mansied

##### Member
Thank you my problem solved by this .

IIf(Month([MONTH])=1 Or Month([MONTH])=3 Or Month([MONTH])=5 Or Month([MONTH])=7 Or Month([MONTH])=8 Or Month([MONTH])=10 Or Month([MONTH])=12 And ([PLANNED] Is Not Null Or [Scheduled] Is Not Null Or [Unscheduled] Is Not Null),((24*31)-([PLANNED]+[Scheduled]+[Unscheduled])),IIf(Month([MONTH])=4 Or Month([MONTH])=6 Or Month([MONTH])=9 Or Month([MONTH])=11 And ([PLANNED] Is Not Null Or [Scheduled] Is Not Null Or [Unscheduled] Is Not Null),((24*30)-([PLANNED]+[Scheduled]+[Unscheduled])),IIf(Month([MONTH])=2 And ([PLANNED] Is Not Null Or [Scheduled] Is Not Null Or [Unscheduled] Is Not Null),((24*29)-([PLANNED]+[Scheduled]+[Unscheduled])),0)))

#### arnelgp

##### ..forever waiting... waiting for jellybean!
you can shorten it further?
Code:
``````(SWITCH(MONTH([MONTH]) IN (1,3,5,7,8,10,12), 31,
MONTH([MONTH]) IN (4,6,9,11), 30, TRUE, 29) * 24) -
NZ([PLANNED] + [SCHEDULED] + [UNSCHEDULED], 0)``````

Replies
8
Views
198
Replies
3
Views
152
Replies
14
Views
525
Replies
4
Views
174
Replies
5
Views
371