Re: Two different totals from one field depending on criteria
- From: Marshall Barton <marshbarton@xxxxxxxxxx>
- Date: Sat, 10 Nov 2007 14:37:24 -0600
Be sure you understand the difference between a field in a
table/query and a control on a form/report. A control can
be bound to a field in the form/report's record source and a
lot of pepole get confused and call the control a field.
This usually doesn't cloud comunications too much because
the resr of the sentence usually provides enough clues to
figure out which meaning is being discussed. In your case,
as for a lot of others, it is critical to understand that
the aggregate functions (Count, Sum, Avg, etc) only operate
on fields, they are unaware of controls.
It is alos important to understand that a text box control
with an aggregate expression need to be in a header/footer
section (but not the page header/footer).
You did not post the expression you used, but I think the
Week1 textbox expression should be like:
=Sum(IIf(Day > 20, Hours, 0))
--
Marsh
MVP [MS Access]
SherryScrapDog wrote:
Thanks for this info! It makes sense to me, but I'm doing something wrong
as I get error as a result. My fields employee, day and hours are all in
the SQL statement for the form; is this what you mean, and are these the
fields you mean when you say they must be in the form's record source table
query? Maybe I need more explantion here?
Then, on the form, for my Week1 textbox, I put the formula (replacing Hours
for somefield) in the Control expression. Maybe this is not right either
because you say not to use a Textbox, but I don't know where else to put the
formula.
I'm sorry for more questions. I sure need to learn more. If you can help
more, thanks! Sherry
"Marshall Barton" wrote:
SherryScrapDog wrote:
I have an employee scheduling database that schedules employees for a 2-week
period. I keep track of the days of the weeks by using 'Days' 11-17 (week 1,
day 1 thru day 7) and 'Days' 21-27 (week 2, day1 thru day 7). I have a
schedule screen for an employee that shows all the days and timeslots they
are scheduled for with a total for the hours at the bottom. However, now the
user would like to have separate totals by week (not a separate schedule,
just the totals). I tried some things but am not experienced enough to
figure out how to get one total (Day < 20) and another total (Day > 20) to
display on the screen. Is this possible? I assume VBA code, but can't
figure out exactly how.
You can use this kind of expression in a form/report
header/footer section to get totals for the all the records
in the form/report. A critical requirement here is that the
fields must be in the form/report's record source table
query, not text box controls in the form/report.
=Sum(IIf(Day < 20, 1, 0))
will count the number of records where the Day field is less
than 20. To conditionally sum a field, you can use:
=Sum(IIf(Day < 20, somefield, 0))
This kind of expression also works in a totals type query.
--
Marsh
MVP [MS Access]
.
- Follow-Ups:
- Re: Two different totals from one field depending on criteria
- From: SherryScrapDog
- Re: Two different totals from one field depending on criteria
- References:
- Re: Two different totals from one field depending on criteria
- From: Marshall Barton
- Re: Two different totals from one field depending on criteria
- Prev by Date: Re: Findfirst NOT working with DAO.Recordset
- Next by Date: Re: return the query result into field within same form
- Previous by thread: Re: Two different totals from one field depending on criteria
- Next by thread: Re: Two different totals from one field depending on criteria
- Index(es):
Relevant Pages
|
Loading
