Solved SUM in a query (1 Viewer)

mansied

Member
Local time
Today, 00:14
Joined
Oct 15, 2020
Messages
98
Hello
I need a help in sum of some fields
I
1632173839586.png

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.
Local time
Today, 00:14
Joined
May 21, 2018
Messages
6,356
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
Local time
Yesterday, 23:14
Joined
May 11, 2011
Messages
10,916
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
Local time
Today, 00:14
Joined
Oct 15, 2020
Messages
98
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!
Local time
Today, 12:14
Joined
May 7, 2009
Messages
16,393
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)
 

Users who are viewing this thread

Top Bottom