I'm trying to use a function like sumif in a query/sql statement 1

[jommyjet]

I'm trying to add a field to a query that will sum monthly entries of varying fields. For instance, now I'd add a total for monthly entries for march and april, but next month, someone will enter data for may, etc...

My select statement that works is

SELECT [MonthlyAssignments].phaseid, Sum([MonthlyAssignments]![Mar2003]+[MonthlyAssignments]![Apr2003]) AS Work
FROM [MonthlyAssignments]
GROUP BY [MonthlyAssignments].phaseid;

but next month will be

SELECT [MonthlyAssignments].phaseid, Sum([MonthlyAssignments]![Mar2003]+[MonthlyAssignments]![Apr2003]+[MonthlyAssignments]![May2003]) AS Work
FROM [MonthlyAssignments]
GROUP BY [MonthlyAssignments].phaseid;

Anyone have any ideas on how I'd sum if all fields that have a certain notation, so I'm sum all fields named ???200#?
thanks for the help

 

[mpastore]

Do the fields [Apr2003],[May2003]....[Dec2003] already exist in your table or do they get added to the structure as time goes on?

 

[jommyjet]

They are in the table, but may have projected values before month end and can't be added unless they are valid. thanks

 

[mpastore]

Paste this into a query:

SELECT Sum(iif(month(date())>1,MonthlyAssignments.Jan2003,0)
+ iif(month(date())>2,MonthlyAssignments.Feb2003,0)
+ iif(month(date())>3,MonthlyAssignments.Mar2003,0)
+ iif(month(date())>4,MonthlyAssignments.Apr2003,0)
+ iif(month(date())>5,MonthlyAssignments.May2003,0)
+ iif(month(date())>6,MonthlyAssignments.Jun2003,0)
+ iif(month(date())>7,MonthlyAssignments.Jul2003,0)
+ iif(month(date())>8,MonthlyAssignments.Aug2003,0)
+ iif(month(date())>9,MonthlyAssignments.Sep2003,0)
+ iif(month(date())>10,MonthlyAssignments.Oct2003,0)
+ iif(month(date())>11,MonthlyAssignments.Nov2003,0)
+ iif(month(date())=1,MonthlyAssignments.Dec2003,0)
) as work
FROM MonthlyAssignments;

 

[jommyjet]

There are null values included in the table, do I need to nest iif functions, like

iif(month(date())>7,iff(isnull(MonthlyAssignments.Jul2003),0,MonthlyAssignments.Jul2003),0)?
or some variant
I imagine this is now more of a nz question. thanks

 

[mpastore]

Just wrap everything with NZ...e.g.

SELECT SUM(
iif(month(date())>1,NZ(MonthlyAssignments.Jan2003,0),0)
+ iif(month(date())>2,NZ(MonthlyAssignments.Feb2003,0),0)
.....

 

[jommyjet]

thanks mpastore, works well