Two different totals from one field depending on criteria

[SherryScrapDog]

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. Thanks for any help! Sherry

 

[Marshall Barton]

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.

 

[SherryScrapDog]

Hi Marshall,
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]

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))

 

[SherryScrapDog]

Thanks. I think I get it now as far as the field versus the control. My
text boxes are in the form footer and I already had (and still have) a text
in the footer for =Sum(Hours) and that worked fine. Now, none of my totals
are working. I will keep trying. Again, thanks because I have more info
than I did! Sherry

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]

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]

It is not unusual for one bad aggregate expression to mess
up the others. Stay concentrated on the one we've been
trying to work out.

If Sum(Hours) worked, it implies that either your syntax is
incorrect (double check the parenthesis, etc) or that Day is
not the name of the **field** in the form's record source
table/query (double check the spelling of the field names).
Or maybe the Hours field in the table is a Text field (or
the foirm's record source is a query that somehow converted
Hours to Text.

If that doesn't clarify things for you, please post a
Copy/Paste of the week1 text box control source expression
along with a Copy/Paste of the form's record source query's
SQL view.

 

[SherryScrapDog]

Hi Marsh, Here is my SQL, then control expression:

SELECT Calendar.Day, Day.Description, Day.Month, Day.DayofMonth, Day.Year,
Employee.Employee AS Employee_Employee, Employee.Name AS Employee_Name,
Home.Name AS Home_Name, Calendar.Number, Calendar.Timeslot, Calendar.Home,
Calendar.Hours, Calendar.Employee AS Calendar_Employee, Employee.Notes
FROM Home INNER JOIN (Employee INNER JOIN ([Day] INNER JOIN Calendar ON
Day.Day = Calendar.Day) ON Employee.Employee = Calendar.Employee) ON
Home.Home = Calendar.Home
ORDER BY Calendar.Day, Calendar.Number;

=Sum(IIf(Calendar.Day<20,Calendar.Hours,0))

I double-checked to make sure Hours is numeric and it is. I tried using
brackets around the table name fields in the expression, ( ) around the name
fields and at first tried Day and Hours (without the Calendar.). I get
#Error in the field.

This form filters for Employee from a previous screen; however, my
Sum(Hours) did work before so I don't know if that could have anything to do
with it.

Thanks so much for this offer of help. I really do enjoy learning more and
more about Access. Sherry

It is not unusual for one bad aggregate expression to mess
up the others. Stay concentrated on the one we've been
trying to work out.

If Sum(Hours) worked, it implies that either your syntax is
incorrect (double check the parenthesis, etc) or that Day is
not the name of the **field** in the form's record source
table/query (double check the spelling of the field names).
Or maybe the Hours field in the table is a Text field (or
the foirm's record source is a query that somehow converted
Hours to Text.

If that doesn't clarify things for you, please post a
Copy/Paste of the week1 text box control source expression
along with a Copy/Paste of the form's record source query's
SQL view.
--
Marsh
MVP [MS Access]

Thanks. I think I get it now as far as the field versus the control. My
text boxes are in the form footer and I already had (and still have) a text
in the footer for =Sum(Hours) and that worked fine. Now, none of my totals
are working. I will keep trying. Again, thanks because I have more info
than I did! Sherry

 

[Marshall Barton]

Hi Marsh, Here is my SQL, then control expression:

SELECT Calendar.Day, Day.Description, Day.Month, Day.DayofMonth, Day.Year,
Employee.Employee AS Employee_Employee, Employee.Name AS Employee_Name,
Home.Name AS Home_Name, Calendar.Number, Calendar.Timeslot, Calendar.Home,
Calendar.Hours, Calendar.Employee AS Calendar_Employee, Employee.Notes
FROM Home INNER JOIN (Employee INNER JOIN ([Day] INNER JOIN Calendar ON
Day.Day = Calendar.Day) ON Employee.Employee = Calendar.Employee) ON
Home.Home = Calendar.Home
ORDER BY Calendar.Day, Calendar.Number;

=Sum(IIf(Calendar.Day<20,Calendar.Hours,0))

I double-checked to make sure Hours is numeric and it is. I tried using
brackets around the table name fields in the expression, ( ) around the name
fields and at first tried Day and Hours (without the Calendar.). I get
#Error in the field.

This form filters for Employee from a previous screen; however, my
Sum(Hours) did work before so I don't know if that could have anything to do
with it.

I don't understand what you mean by "This form filters for
Employee from a previous screen". I don't see any filtering
in your query.

Well, your syntax looks ok, BUT ...

Just because the value in the Day field looks like a number
doesn't mean it is not a Text value. Make sure you have a
text box in the form's detail section that's bound to the
Day field and that it has nothing in its Format property.
Then switch the form to form view and check if the value is
right or left aligned. If it's left aligned, then Access
thinks it's a Text value. Do the same thing for the Hours
field.

One other possible problem is your use of reserved words for
both table and field names. I can't be sure it is a problem
here, but I'm pretty sure it will be a problem somewhere,
sometime. Allen Browne has the most complete list of
reserved words at
http://allenbrowne.com/AppIssueBadWord.html

I noticed that the MS list (not as complete as Allen's) at
http://support.microsoft.com/kb/248738/en-us
lists Day as a reserved word in a query, so, maybe(?) your
use of Day as both a table and a field name are a problem.

You have several other names (Month, Name, Year, Number,
???) that are also reserved words so check the list
carefully and (I hate to say it) change all those names to
something else. I can't be bothered ckecking such huge
lists, so I just make sure I don't use a real word as a
name.

Several of your names are also reserved words in VBA, so
even if it sneaks by in your query, it might blowup in an
Access expression or in some VBA procedure.

Note: you only need to use a table name in front of a field
name when there are two fields, e.g. Day, with the same name
in a query. That in itself is why having a field in one
table that is named the same as a field in another table is
not a good idea. You might want to consider adopting a
naming convention that avoids all these issues
http://www.granite.ab.ca/access/tablefieldnaming.htm

 

[SherryScrapDog]

Thanks for all this info, I will check it out! Since I haven't done hardly
any VBA coding, I didn't know about the reserved words. I've read a few
books on Access and I am just now reading one that explains about VBA. This
system is not that big, so I could bite the bullet and change the names.
This is the first I have heard about making the names different in tables; I
thought they should be the same to link them, although I know I use the
relationship to choose which fields are related. FYI: What I meant by the
filter is, I have a form where I list all of the employees, then a command
button takes me to this form. I have noticed that there is VBA created in
the form with the command button when I do this that tells it to choose which
employee. I really appreciate all of the time you have spent explaining
things to me! Sherry

Hi Marsh, Here is my SQL, then control expression:

SELECT Calendar.Day, Day.Description, Day.Month, Day.DayofMonth, Day.Year,
Employee.Employee AS Employee_Employee, Employee.Name AS Employee_Name,
Home.Name AS Home_Name, Calendar.Number, Calendar.Timeslot, Calendar.Home,
Calendar.Hours, Calendar.Employee AS Calendar_Employee, Employee.Notes
FROM Home INNER JOIN (Employee INNER JOIN ([Day] INNER JOIN Calendar ON
Day.Day = Calendar.Day) ON Employee.Employee = Calendar.Employee) ON
Home.Home = Calendar.Home
ORDER BY Calendar.Day, Calendar.Number;

=Sum(IIf(Calendar.Day<20,Calendar.Hours,0))

I double-checked to make sure Hours is numeric and it is. I tried using
brackets around the table name fields in the expression, ( ) around the name
fields and at first tried Day and Hours (without the Calendar.). I get
#Error in the field.

This form filters for Employee from a previous screen; however, my
Sum(Hours) did work before so I don't know if that could have anything to do
with it.

I don't understand what you mean by "This form filters for
Employee from a previous screen". I don't see any filtering
in your query.

Well, your syntax looks ok, BUT ...

Just because the value in the Day field looks like a number
doesn't mean it is not a Text value. Make sure you have a
text box in the form's detail section that's bound to the
Day field and that it has nothing in its Format property.
Then switch the form to form view and check if the value is
right or left aligned. If it's left aligned, then Access
thinks it's a Text value. Do the same thing for the Hours
field.

One other possible problem is your use of reserved words for
both table and field names. I can't be sure it is a problem
here, but I'm pretty sure it will be a problem somewhere,
sometime. Allen Browne has the most complete list of
reserved words at
http://allenbrowne.com/AppIssueBadWord.html

I noticed that the MS list (not as complete as Allen's) at
http://support.microsoft.com/kb/248738/en-us
lists Day as a reserved word in a query, so, maybe(?) your
use of Day as both a table and a field name are a problem.

You have several other names (Month, Name, Year, Number,
???) that are also reserved words so check the list
carefully and (I hate to say it) change all those names to
something else. I can't be bothered ckecking such huge
lists, so I just make sure I don't use a real word as a
name.

Several of your names are also reserved words in VBA, so
even if it sneaks by in your query, it might blowup in an
Access expression or in some VBA procedure.

Note: you only need to use a table name in front of a field
name when there are two fields, e.g. Day, with the same name
in a query. That in itself is why having a field in one
table that is named the same as a field in another table is
not a good idea. You might want to consider adopting a
naming convention that avoids all these issues
http://www.granite.ab.ca/access/tablefieldnaming.htm

 

[Marshall Barton]

Whoops, I meant to suggest that you make all those name
changes in a copy of your database.

I sure hope that going through the tedious and frustrating
exercise of changing so many names resolves your original
problem. I can assure you that it will avoid many other
problems you probably have not seen yet.

Good Luck
--
Marsh
MVP [MS Access]

Thanks for all this info, I will check it out! Since I haven't done hardly
any VBA coding, I didn't know about the reserved words. I've read a few
books on Access and I am just now reading one that explains about VBA. This
system is not that big, so I could bite the bullet and change the names.
This is the first I have heard about making the names different in tables; I
thought they should be the same to link them, although I know I use the
relationship to choose which fields are related. FYI: What I meant by the
filter is, I have a form where I list all of the employees, then a command
button takes me to this form. I have noticed that there is VBA created in
the form with the command button when I do this that tells it to choose which
employee. I really appreciate all of the time you have spent explaining
things to me! Sherry

Hi Marsh, Here is my SQL, then control expression:

SELECT Calendar.Day, Day.Description, Day.Month, Day.DayofMonth, Day.Year,
Employee.Employee AS Employee_Employee, Employee.Name AS Employee_Name,
Home.Name AS Home_Name, Calendar.Number, Calendar.Timeslot, Calendar.Home,
Calendar.Hours, Calendar.Employee AS Calendar_Employee, Employee.Notes
FROM Home INNER JOIN (Employee INNER JOIN ([Day] INNER JOIN Calendar ON
Day.Day = Calendar.Day) ON Employee.Employee = Calendar.Employee) ON
Home.Home = Calendar.Home
ORDER BY Calendar.Day, Calendar.Number;

=Sum(IIf(Calendar.Day<20,Calendar.Hours,0))

I double-checked to make sure Hours is numeric and it is. I tried using
brackets around the table name fields in the expression, ( ) around the name
fields and at first tried Day and Hours (without the Calendar.). I get
#Error in the field.

This form filters for Employee from a previous screen; however, my
Sum(Hours) did work before so I don't know if that could have anything to do
with it.

I don't understand what you mean by "This form filters for
Employee from a previous screen". I don't see any filtering
in your query.

Well, your syntax looks ok, BUT ...

Just because the value in the Day field looks like a number
doesn't mean it is not a Text value. Make sure you have a
text box in the form's detail section that's bound to the
Day field and that it has nothing in its Format property.
Then switch the form to form view and check if the value is
right or left aligned. If it's left aligned, then Access
thinks it's a Text value. Do the same thing for the Hours
field.

One other possible problem is your use of reserved words for
both table and field names. I can't be sure it is a problem
here, but I'm pretty sure it will be a problem somewhere,
sometime. Allen Browne has the most complete list of
reserved words at
http://allenbrowne.com/AppIssueBadWord.html

I noticed that the MS list (not as complete as Allen's) at
http://support.microsoft.com/kb/248738/en-us
lists Day as a reserved word in a query, so, maybe(?) your
use of Day as both a table and a field name are a problem.

You have several other names (Month, Name, Year, Number,
???) that are also reserved words so check the list
carefully and (I hate to say it) change all those names to
something else. I can't be bothered ckecking such huge
lists, so I just make sure I don't use a real word as a
name.

Several of your names are also reserved words in VBA, so
even if it sneaks by in your query, it might blowup in an
Access expression or in some VBA procedure.

Note: you only need to use a table name in front of a field
name when there are two fields, e.g. Day, with the same name
in a query. That in itself is why having a field in one
table that is named the same as a field in another table is
not a good idea. You might want to consider adopting a
naming convention that avoids all these issues
http://www.granite.ab.ca/access/tablefieldnaming.htm