SUMIF

[Tom Cunningham]

I hope someone can help me with this, it's driving me crazy.
I have four queries, each containing students scores which are grouped by week using the DatePart function. I would like to create a query to total scores from each query by week. Basicall, IF Week = 10 SUM QueryA, QueryB, QueryC, QueryD.
Here is the actual expression I have put together thus far: Week_Ten: If([Qry_WeekTotal_TimeTableA]![Week]=10),Sum[Qry_WeekTotal_TimeTableA]![WeekTotal_A],[Qry_WeekTotal_TimeTableB]![WeekTotal_B],[Qry_WeekTotal_TimeTableC]![WeekTotal_C],[Qry_WeekTotal_TimeTableD]![WeekTotal_D])null)
I would like to use the SUMIF function rather than the WHERE = 10 in the criteria because would like to create several colums of totals for each week. Using the WHERE will not let me total multiple weeks.
Thank you in advance for your assistance.
Tom

 

[Bob Barrows]

I hope someone can help me with this, it's driving me crazy.
I have four queries, each containing students scores which are
grouped by week using the DatePart function. I would like to create a
query to total scores from each query by week. Basicall, IF Week = 10
SUM QueryA, QueryB, QueryC, QueryD.
Here is the actual expression I have put together thus far: Week_Ten:
If([Qry_WeekTotal_TimeTableA]![Week]=10),Sum[Qry_WeekTotal_TimeTableA]![WeekTotal_A],[Qry_WeekTotal_TimeTableB]![WeekTotal_B],[Qry_WeekTotal_TimeTableC]![WeekTotal_C],[Qry_WeekTotal_TimeTableD]![WeekTotal_D])null)
I would like to use the SUMIF function rather than the WHERE = 10 in
the criteria because would like to create several colums of totals
for each week. Using the WHERE will not let me total multiple weeks.
Thank you in advance for your assistance.
Tom

Errr ... what is a "SUMIF" function? You've used IIF here, but incorrectly,
given that you left out the comma before the null (in a query, only two
arguments are required, so you don't even need to specify the null). Also,
the SUM aggregate function can only take a single argument - that's not to
say it can only deal with one field. You can use an expression referring to
multiple fields, like this:
If([Qry_WeekTotal_TimeTableA]![Week]=10),Sum[Qry_WeekTotal_TimeTableA]![WeekTotal_A]+[Qry_WeekTotal_TimeTableB]![WeekTotal_B]+[Qry_WeekTotal_TimeTableC]![WeekTotal_C]+,[Qry_WeekTotal_TimeTableD]![WeekTotal_D]))

As always, if this does not meet your needs, provide sample data and desired
results in tabular format to help us out.

 

[Tom Cunningham]

Thanks for your help!
When I try running the expression using the example you provided I get: "The expression you entered contains invalid syntax, or you need to enclose your text in quotes."
Any ideas about what I might be doing wrong?
Thanks for your help!
Tom

 

[Bob Barrows]

Thanks for your help!
When I try running the expression using the example you provided I
get: "The expression you entered contains invalid syntax, or you need
to enclose your text in quotes." Any ideas about what I might be
doing wrong?

Not until you show me what you did ...

I just looked at what I posted and saw I left in a comma I had meant to
delete.

ekTotal_C]+,[Qry_WeekTota
^
should have been
ekTotal_C]+[Qry_WeekTota



Hope you didn't copy-paste it. You were meant to look at it and see the idea
of what I was recommending and apply it to your query.