Sum questions

[Joe C]

Hello and pardon the pun,

I have a standard table. In a particular field there can be two values, A or
B. "A" or "B" have number values associated to them. In the report's footer.
For example:

a=8
b=5
a=4
b=5

Total
a=12
b=10

Any idea?

Thanks
Joe
Papi

 

[Damian S]

Hi Joe,

Assuming your table has fields FIELDTYPE (a or b etc) and FIELDVALUE (4, 5,
8 etc)you would set your report to group by FIELDTYPE, then put FIELDVALUE on
your report in the details section. This would group all A's together, all
B's together etc.

In the report footer, have a control that has =sum(FIELDVALUE) as the
control source and it will sum all the values across the whole report. If
you want to have sum of the FIELDTYPE's separately, have it in the group
footer.

If you are only interested in seeing the totals, simply have your query
structured like this:

select FIELDTYPE, sum(FIELDVALUE) as TotalFieldValue from TABLENAME group by
FIELDTYPE

Hope this information points you in the right direction.

Damian.

 

[Joe C]

Damian,

That is what I thought I had to do. The requirements given to me were to
have the totals on the same section, similar to two Excel sumif functions on
the same line. My boss seems to think I am a magician. Thanks to you guys I
have been but the magic is limited.

Thanks for you help and let me know if you have any ideas.

JC

 

[Marshall Barton]

If you only have a very limited number of Types and they are
very unlikely to change, then you can use the quick a dirty
approach. The equivalant of Excel's SumIF in Access is
along these lines: Sum(IIf(condition, truevalue, 0))

This is not a viable approach for general situations because
the condition is data dependent and would have to be
modified when a new type is added to the table.

 

[Joe C]

Marshall,

I actually only have two variables, Sick and Vacation. Please give me a
quick once over on this. I would have a list of the records. at the end of
the given dataset I would be able to put two of theese sumif things next to
each other and one would calculate value A and the other value B?

On another note I was just handed another problem. I need a running total of
these same variables before we get to the end sumif stuff. Any thoughts?

I really appreciate your help.

JC
Papi

If you only have a very limited number of Types and they are
very unlikely to change, then you can use the quick a dirty
approach. The equivalant of Excel's SumIF in Access is
along these lines: Sum(IIf(condition, truevalue, 0))

This is not a viable approach for general situations because
the condition is data dependent and would have to be
modified when a new type is added to the table.
--
Marsh
MVP [MS Access]

That is what I thought I had to do. The requirements given to me were to
have the totals on the same section, similar to two Excel sumif functions on
the same line. My boss seems to think I am a magician. Thanks to you guys I
have been but the magic is limited.

 

[Marshall Barton]

Try to be more specific. What are the names of the fields
and the values that determine which sum? Without these
details we can't provide much more than vague examples.

I this case, I guess it would something along these lines:
=Sum(IIf(somefield="A", Sick, 0))

Can't really say anything specific about what you should do
about a running total of something or other. Try add adding
a text box bound(?) to the field and set the text box's
RunningSum property to Over All or Over Group as appropriate
to whatever it is you need.

 

[Joe C]

Marshall,

You the man!!!!

Thanks
JC

Try to be more specific. What are the names of the fields
and the values that determine which sum? Without these
details we can't provide much more than vague examples.

I this case, I guess it would something along these lines:
=Sum(IIf(somefield="A", Sick, 0))

Can't really say anything specific about what you should do
about a running total of something or other. Try add adding
a text box bound(?) to the field and set the text box's
RunningSum property to Over All or Over Group as appropriate
to whatever it is you need.
--
Marsh
MVP [MS Access]

I actually only have two variables, Sick and Vacation. Please give me a
quick once over on this. I would have a list of the records. at the end of
the given dataset I would be able to put two of theese sumif things next to
each other and one would calculate value A and the other value B?

 

[Joe C]

Marsh,

I take that back, not only are you the man but you are back in the will!!!!

Joe C

Marshall,

You the man!!!!

Thanks
JC

 

[Marshall Barton]

ROFLOL

Runing in circles, jumping up and shouting Hoorah, Da man is
in the will, Da man is in the will, High ho, High ho, Da man
is in the will.

I don't even know what the question was, but I must have
said something useful somwhere along the way ;-)
--
Marsh

Marsh,

I take that back, not only are you the man but you are back in the will!!!!

Joe C