An odd =Sum() question?

[Flopbot]

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[KARL DEWEY]

This kind of math can be done in a query. <<Only add up those hours worked in

the last 365 days.>> You have to have a field for dates but you did not mention any.

You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[John W. Vinson]

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Form”) with an attached subform (“Volunteer Hours Subform”). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total” on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all easily. I'd
suggest using the DSum() function to go directly to the table, or creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

 

[Flopbot]

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform†comes
from a “Volunteer Hours Tracking Tableâ€. Both the table and the subform have
the following fields: “Date Worked†(ex. 9/26/2008, 9/26/2003), “Start Timeâ€
(ex. 12:00pm), “End Time†(ex. 5:00pm). On my subform I also have one extra
field “Totalsâ€, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form†and it comes from the
“Volunteer Info Tableâ€. Both tables are linked by a “Volunteer IDâ€.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

This kind of math can be done in a query. <<Only add up those hours worked in

the last 365 days.>> You have to have a field for dates but you did not mention any.

You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[Flopbot]

Thanks for helping me also!!! Is my thinking completely nuts on this? The
following function didn’t work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because I
haven’t found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment are
beyond me. Hopefully, I’ll play with them tonight and see what I can learn.

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all easily. I'd
suggest using the DSum() function to go directly to the table, or creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

DateAdd("yyyy", -1, Date())

 

[KARL DEWEY]

Both the table and the subform have the following fields: “Date Workedâ€
(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform†comes
from a “Volunteer Hours Tracking Tableâ€. Both the table and the subform have
the following fields: “Date Worked†(ex. 9/26/2008, 9/26/2003), “Start Timeâ€
(ex. 12:00pm), “End Time†(ex. 5:00pm). On my subform I also have one extra
field “Totalsâ€, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form†and it comes from the
“Volunteer Info Tableâ€. Both tables are linked by a “Volunteer IDâ€.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

This kind of math can be done in a query. <<Only add up those hours worked in

the last 365 days.>> You have to have a field for dates but you did not mention any.

You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[Flopbot]

No, it only has one date per cell. I was just trying to show that the entire
database has dates going back many years. The datatype for that field is
Date/Time. Is a query something that will allow me to show a result on my
main form or will I have to go someplace else to get that result. I'm going
to play around with queries tonight.

(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform†comes
from a “Volunteer Hours Tracking Tableâ€. Both the table and the subform have
the following fields: “Date Worked†(ex. 9/26/2008, 9/26/2003), “Start Timeâ€
(ex. 12:00pm), “End Time†(ex. 5:00pm). On my subform I also have one extra
field “Totalsâ€, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form†and it comes from the
“Volunteer Info Tableâ€. Both tables are linked by a “Volunteer IDâ€.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

This kind of math can be done in a query. <<Only add up those hours worked in
the last 365 days.>> You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[KARL DEWEY]

Post some sample data.
--
KARL DEWEY
Build a little - Test a little

No, it only has one date per cell. I was just trying to show that the entire
database has dates going back many years. The datatype for that field is
Date/Time. Is a query something that will allow me to show a result on my
main form or will I have to go someplace else to get that result. I'm going
to play around with queries tonight.

Both the table and the subform have the following fields: “Date Workedâ€

(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform†comes
from a “Volunteer Hours Tracking Tableâ€. Both the table and the subform have
the following fields: “Date Worked†(ex. 9/26/2008, 9/26/2003), “Start Timeâ€
(ex. 12:00pm), “End Time†(ex. 5:00pm). On my subform I also have one extra
field “Totalsâ€, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form†and it comes from the
“Volunteer Info Tableâ€. Both tables are linked by a “Volunteer IDâ€.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

:

This kind of math can be done in a query. <<Only add up those hours worked in
the last 365 days.>> You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[John W. Vinson]

Thanks for helping me also!!! Is my thinking completely nuts on this? The
following function didn’t work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because I
haven’t found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment are
beyond me. Hopefully, I’ll play with them tonight and see what I can learn.

Queries are *ABSOLUTELY BASIC* to any productive use of Access. If you can't
do queries you can't use Access!

If the End Time and Start Time are Date/Time fields, then you can use the
DateDiff() function to calculate the number of hours. Assuming that you want
hours and fractions of hours (e.g. if someone worked 4 hours 15 minutes you
want 4.25) then calculate the time in minutes and divide.

Your DSum() will need a third operand to limit the sum to this particular
volunteer. Not knowing anything about your table structure I'll have to make a
wild guess: maybe something like

=DSum("DateDiff(""n"",[Start Time],[End Time])/60)","[Volunteer Hours Tracking
Table"], "VolunteerID = " & [VolunteerID] & " AND [datefield] >=
DateAdd(""yyyy"", -1, Date())

 

[Rockn]

Not a good idea to calculate fields within a table if the raw data is
already there and can be displayed as calculated in queries and reports.

You need to filter the results you want to be displayed first which would be
the records for people that have worked within the last year. Once you have
the recordset you are looking for then you can build a form or whatever
based on the filtered results. Build the query first and go from there.

Thanks for helping me also!!! Is my thinking completely nuts on this?
The
following function didn't work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and
was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because
I
haven't found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment
are
beyond me. Hopefully, I'll play with them tonight and see what I can
learn.

Here it goes. I've reworded this question. I have a form ("Volunteer
Database Form") with an attached subform ("Volunteer Hours Subform"). I
would like to add a control box to my form with an expression that will
do
two things. 1.) Pull its data from a calculated field labeled "Total"
on
the subform. This field's equation {=([End Time]-[Start Time])*24)}
results
in the total hours worked each day. 2.) Only add up those hours worked
in
the last 365 days. If it works correctly, the equation should return
the
total number of hours worked by one person in the past year. Does
anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

You cannot do this using the data on the form, at least not at all
easily. I'd
suggest using the DSum() function to go directly to the table, or
creating a
Totals query with a criterion on the volunteer ID such as

=[Forms]![YourFormName]![YourControlName]

and on the date field of

DateAdd("yyyy", -1, Date())

 

[Flopbot]

John,

Sorry it’s taken so long for me to respond. I wanted to play around with
queries to get a better idea of how they work. I did make a query that only
returns the dates worked over the past year. The problem is that it doesn’t
calculate the total for each individual person. The other problem is that I
want the end user (myself) to never need to stray from the main form so I
really like your idea of a specific calculation stored on the main form.
When I entered your calculation (below) into my box, it pops up a warning box
that says “The expression you entered has a function containing the wrong
number of arguments.†Any ideas? Thanks!

=DSum(DateDiff(“â€nâ€â€,[Start Time],[End Time])/60)â€,â€[Volunteer Hours
Tracking Tableâ€], “Volunteer ID = “ & [Volunteer ID] & “ AND [Date Worked] >=
DateAdd(“â€yyyyâ€â€, -1, Date())

Thanks for helping me also!!! Is my thinking completely nuts on this? The
following function didn’t work, but am I close =DSum("([End Time]-[Start
Time])*24)","Volunteer Hours Tracking Table"). I used your =DSum() and was
trying to get it to add up the total hours worked each day by using the
equation ([End Time]-[Start Time])*24) in place of an actual field because I
haven’t found a way to create calculated field in a table. . .only in
subtables. My thinking is that if I can get this equation to work, then
maybe I could somehow set limits on what it adds. Queries at the moment are
beyond me. Hopefully, I’ll play with them tonight and see what I can learn.

Queries are *ABSOLUTELY BASIC* to any productive use of Access. If you can't
do queries you can't use Access!

If the End Time and Start Time are Date/Time fields, then you can use the
DateDiff() function to calculate the number of hours. Assuming that you want
hours and fractions of hours (e.g. if someone worked 4 hours 15 minutes you
want 4.25) then calculate the time in minutes and divide.

Your DSum() will need a third operand to limit the sum to this particular
volunteer. Not knowing anything about your table structure I'll have to make a
wild guess: maybe something like

=DSum("DateDiff(""n"",[Start Time],[End Time])/60)","[Volunteer Hours Tracking
Table"], "VolunteerID = " & [VolunteerID] & " AND [datefield] >=
DateAdd(""yyyy"", -1, Date())

 

[John W. Vinson]

John,

Sorry it’s taken so long for me to respond. I wanted to play around with
queries to get a better idea of how they work. I did make a query that only
returns the dates worked over the past year. The problem is that it doesn’t
calculate the total for each individual person. The other problem is that I
want the end user (myself) to never need to stray from the main form so I
really like your idea of a specific calculation stored on the main form.
When I entered your calculation (below) into my box, it pops up a warning box
that says “The expression you entered has a function containing the wrong
number of arguments.” Any ideas? Thanks!

=DSum(DateDiff(“”n””,[Start Time],[End Time])/60)”,”[Volunteer Hours
Tracking Table”], “Volunteer ID = “ & [Volunteer ID] & “ AND [Date Worked] >=
DateAdd(“”yyyy””, -1, Date())

I'm not sure where you're getting the "smart quotes" (“” instead of ") but I
don't think they'll work... especially not when some are inside brackets, some
outside, some doubled, etc.

Try this: create a Query based on Volunteer Hours Tracking Table, let's call
it qryHours. In it put a calculated field

WorkedHours: DateDiff("n",[Start Time],[End Time])/60)

Put a criterion on Date Worked of

DateAdd("yyyy", -1, Date())

to get just those records between 10/15/07 and 10/15/08 (or whatever date the
query is run).

Include the VolunteerID field. Make it a Totals query; Group By the
VolunteerID, change the Group By to "Where" under the date, and use Sum under
the WorkedHours field.

You can then use

=DLookUp("SumOfWorkedHours", "qryHours", "VolunteerID = " & [VolunteerID])

as the control source of a textbox on your form.

 

[Flopbot]

Karl,

Sorry about that. I took a short break to learn about queries! To answer
your request, here’s some sample data. It’s pretty much the same as above.
Let me know if you meant something different.

Fields on both “Volunteer Hours Subform†and “Volunteer Hours Tracking Tableâ€:

"Volunteer ID" (ex. 1: Auto Number)
“Date Worked†(ex. 9/26/2003: Date/Time Format)
“Start Time†(ex. 12:00pm: Date/Time Format)
“End Time†(ex. 5:00pm: Date/Time Format).

Field only on subform:

“Total†{ =DateDiff("n",[Start Time],[End Time])/60 } A calculated
field that finds the total number of hours worked. This expression has
changed since my original post. Older and wiser now.

If done right, I want the expression to tell me that according to the above
data, this volunteer #1 worked a total of 5 hours.

Post some sample data.
--
KARL DEWEY
Build a little - Test a little

No, it only has one date per cell. I was just trying to show that the entire
database has dates going back many years. The datatype for that field is
Date/Time. Is a query something that will allow me to show a result on my
main form or will I have to go someplace else to get that result. I'm going
to play around with queries tonight.

Both the table and the subform have the following fields: “Date Workedâ€
(ex. 9/26/2008, 9/26/2003),
What is the datatype of your [Date Worked] field? If it has two dates in it
like your example then it is text.
What is the datatype
What you need is a query between the table and forms.

--
KARL DEWEY
Build a little - Test a little


:

Thank you so much for your help!!! Sorry, I’m really new to Access so I
don’t yet know anything about queries. I was hoping that the number could be
calculated directly on the form instead of searching for in another window.
Is that what a query would make me do? My “Volunteer Hours Subform†comes
from a “Volunteer Hours Tracking Tableâ€. Both the table and the subform have
the following fields: “Date Worked†(ex. 9/26/2008, 9/26/2003), “Start Timeâ€
(ex. 12:00pm), “End Time†(ex. 5:00pm). On my subform I also have one extra
field “Totalsâ€, that calculates the number of hours worked between the start
and end times (Returns: 5 hours) each day. The number 5 results from the
formula =(([End Time]-[Start Time])*24). I’m not sure why that formula
works, but it does! I’ve also used something similar in Excel with success.
The main form is called the “Volunteer Database Form†and it comes from the
“Volunteer Info Tableâ€. Both tables are linked by a “Volunteer IDâ€.
Basically, from the above example, I want the expression to add up the number
of hours worked (5) over the past year (excluding 9/26/2003). Thank you
again for your help!!!

:

This kind of math can be done in a query. <<Only add up those hours worked in
the last 365 days.>> You have to have a field for dates but you did not mention any.
You talk about your forms but nothing about your tables and fields. What
are the names and datatype? Post some sample data.

I do not understand your formula =([End Time]-[Start Time])*24)
Can you tell me what is the purpose of the *24 in it? Now if [End
Time]-[Start Time] resulted in hours you would divide by 24 to get days.
--
KARL DEWEY
Build a little - Test a little


:

Here it goes. I’ve reworded this question. I have a form (“Volunteer
Database Formâ€) with an attached subform (“Volunteer Hours Subformâ€). I
would like to add a control box to my form with an expression that will do
two things. 1.) Pull its data from a calculated field labeled “Total†on
the subform. This field’s equation {=([End Time]-[Start Time])*24)} results
in the total hours worked each day. 2.) Only add up those hours worked in
the last 365 days. If it works correctly, the equation should return the
total number of hours worked by one person in the past year. Does anyone
know an expression that I can use to do this? From the Access 2003 help
menus, I think it might be some combination of the following expressions
{=Sum()} & {<Date()-365}. Thank you in advance!!!!

 

[Flopbot]

John,

You are amazing! It worked! Thank you sooooo much! You’re right about
queries that you really can’t get by without them. I didn’t know you can do
calculations and then actually link your query to a form. Thank you for
taking your time to explain how they work. Now that I know queries exist, I
think there are a few more things I want to try!

John,

Sorry it’s taken so long for me to respond. I wanted to play around with
queries to get a better idea of how they work. I did make a query that only
returns the dates worked over the past year. The problem is that it doesn’t
calculate the total for each individual person. The other problem is that I
want the end user (myself) to never need to stray from the main form so I
really like your idea of a specific calculation stored on the main form.
When I entered your calculation (below) into my box, it pops up a warning box
that says “The expression you entered has a function containing the wrong
number of arguments.†Any ideas? Thanks!

=DSum(DateDiff(“â€nâ€â€,[Start Time],[End Time])/60)â€,â€[Volunteer Hours
Tracking Tableâ€], “Volunteer ID = “ & [Volunteer ID] & “ AND [Date Worked] >=
DateAdd(“â€yyyyâ€â€, -1, Date())

I'm not sure where you're getting the "smart quotes" (Ҡinstead of ") but I
don't think they'll work... especially not when some are inside brackets, some
outside, some doubled, etc.

Try this: create a Query based on Volunteer Hours Tracking Table, let's call
it qryHours. In it put a calculated field

WorkedHours: DateDiff("n",[Start Time],[End Time])/60)

Put a criterion on Date Worked of

DateAdd("yyyy", -1, Date())

to get just those records between 10/15/07 and 10/15/08 (or whatever date the
query is run).

Include the VolunteerID field. Make it a Totals query; Group By the
VolunteerID, change the Group By to "Where" under the date, and use Sum under
the WorkedHours field.

You can then use

=DLookUp("SumOfWorkedHours", "qryHours", "VolunteerID = " & [VolunteerID])

as the control source of a textbox on your form.

 

[Flopbot]

John,

I guess I just found one more question related to that query. Sorry, this
will be the last. Up until now, I’ve been using whole numbers; however, I
entered a volunteer who worked 5.5 hours. He also worked on a different day
for 5 hours. (Total: 10.5 hours) In the results for qryHours, this
volunteer shows up on two separate lines instead of one line with the 10.5
total. (Line 1: Volunteer ID = 2, Total = 5) (Line 2: Volunteer ID = 2,
Total = 5.5) In the box on the main form (expression below), it only shows
that Volunteer ID #2 worked 5 hours last year, not 10.5 hours. Any ideas?

=DLookUp("Total","Hours Worked Last Year Query","[Volunteer ID] = " &
[Volunteer ID])

John,

Sorry it’s taken so long for me to respond. I wanted to play around with
queries to get a better idea of how they work. I did make a query that only
returns the dates worked over the past year. The problem is that it doesn’t
calculate the total for each individual person. The other problem is that I
want the end user (myself) to never need to stray from the main form so I
really like your idea of a specific calculation stored on the main form.
When I entered your calculation (below) into my box, it pops up a warning box
that says “The expression you entered has a function containing the wrong
number of arguments.†Any ideas? Thanks!

=DSum(DateDiff(“â€nâ€â€,[Start Time],[End Time])/60)â€,â€[Volunteer Hours
Tracking Tableâ€], “Volunteer ID = “ & [Volunteer ID] & “ AND [Date Worked] >=
DateAdd(“â€yyyyâ€â€, -1, Date())

I'm not sure where you're getting the "smart quotes" (Ҡinstead of ") but I
don't think they'll work... especially not when some are inside brackets, some
outside, some doubled, etc.

Try this: create a Query based on Volunteer Hours Tracking Table, let's call
it qryHours. In it put a calculated field

WorkedHours: DateDiff("n",[Start Time],[End Time])/60)

Put a criterion on Date Worked of

DateAdd("yyyy", -1, Date())

to get just those records between 10/15/07 and 10/15/08 (or whatever date the
query is run).

Include the VolunteerID field. Make it a Totals query; Group By the
VolunteerID, change the Group By to "Where" under the date, and use Sum under
the WorkedHours field.

You can then use

=DLookUp("SumOfWorkedHours", "qryHours", "VolunteerID = " & [VolunteerID])

as the control source of a textbox on your form.

 

[John W. Vinson]

John,

I guess I just found one more question related to that query. Sorry, this
will be the last. Up until now, I’ve been using whole numbers; however, I
entered a volunteer who worked 5.5 hours. He also worked on a different day
for 5 hours. (Total: 10.5 hours) In the results for qryHours, this
volunteer shows up on two separate lines instead of one line with the 10.5
total. (Line 1: Volunteer ID = 2, Total = 5) (Line 2: Volunteer ID = 2,
Total = 5.5) In the box on the main form (expression below), it only shows
that Volunteer ID #2 worked 5 hours last year, not 10.5 hours. Any ideas?

=DLookUp("Total","Hours Worked Last Year Query","[Volunteer ID] = " &
[Volunteer ID])

It sounds like you're doing a totals query and grouping by the hours worked.
Perhaps you could post the actual current SQL view of qryHours. It is not the
DSum expression, unless I'm missing something.

 

[Flopbot]

Here’s how the query is set up. It’s called “qryHoursâ€.

FIELD: CRITERIA: TOTAL:

Volunteer ID Group By
Number of People Group By
Date Worked >DateAdd("yyyy",-1,Date()) Where
Start Time Group By
End Time Group By
Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])
Expression

I tried changing the TOTAL: box for the “Date Worked†field to “Group Byâ€
and “Sum†and that didn’t fix it. I also tried changing the TOTAL: box for
the “Volunteer ID†field to “Where†and that didn’t fix it. In fact, when I
did it, the volunteer ID field disappeared.

John,

I guess I just found one more question related to that query. Sorry, this
will be the last. Up until now, I’ve been using whole numbers; however, I
entered a volunteer who worked 5.5 hours. He also worked on a different day
for 5 hours. (Total: 10.5 hours) In the results for qryHours, this
volunteer shows up on two separate lines instead of one line with the 10.5
total. (Line 1: Volunteer ID = 2, Total = 5) (Line 2: Volunteer ID = 2,
Total = 5.5) In the box on the main form (expression below), it only shows
that Volunteer ID #2 worked 5 hours last year, not 10.5 hours. Any ideas?

=DLookUp("Total","Hours Worked Last Year Query","[Volunteer ID] = " &
[Volunteer ID])

It sounds like you're doing a totals query and grouping by the hours worked.
Perhaps you could post the actual current SQL view of qryHours. It is not the
DSum expression, unless I'm missing something.

 

[John W. Vinson]

Here’s how the query is set up. It’s called “qryHours”.

FIELD: CRITERIA: TOTAL:

Volunteer ID Group By
Number of People Group By
Date Worked >DateAdd("yyyy",-1,Date()) Where
Start Time Group By
End Time Group By
Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])
Expression

I tried changing the TOTAL: box for the “Date Worked” field to “Group By”
and “Sum” and that didn’t fix it. I also tried changing the TOTAL: box for
the “Volunteer ID” field to “Where” and that didn’t fix it. In fact, when I
did it, the volunteer ID field disappeared.

This is unreadable, Flopbot. Please open the query in design view; select
View...SQL; and copy and paste the SQL text to a message here. It may look
cryptic but that's the real query and we'll be able to get your question
answered easily.

 

[Flopbot]

OK, thank you for hanging in there!

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Hours Tracking Table]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("yyyy",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Number of People], [Volunteer Hours Tracking Table].[Start
Time], [Volunteer Hours Tracking Table].[End Time];

Here’s how the query is set up. It’s called “qryHoursâ€.

FIELD: CRITERIA: TOTAL:

Volunteer ID Group By
Number of People Group By
Date Worked >DateAdd("yyyy",-1,Date()) Where
Start Time Group By
End Time Group By
Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])
Expression

I tried changing the TOTAL: box for the “Date Worked†field to “Group Byâ€
and “Sum†and that didn’t fix it. I also tried changing the TOTAL: box for
the “Volunteer ID†field to “Where†and that didn’t fix it. In fact, when I
did it, the volunteer ID field disappeared.

This is unreadable, Flopbot. Please open the query in design view; select
View...SQL; and copy and paste the SQL text to a message here. It may look
cryptic but that's the real query and we'll be able to get your question
answered easily.

 

[John W. Vinson]

OK, thank you for hanging in there!

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Hours Tracking Table]
WHERE ((([Volunteer Hours Tracking Table].[Date
Worked])>DateAdd("yyyy",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID], [Volunteer Hours
Tracking Table].[Number of People], [Volunteer Hours Tracking Table].[Start
Time], [Volunteer Hours Tracking Table].[End Time];

It sounds like you're grouping by the Start Time and End Time rather than just
summing them. Try

SELECT [Volunteer Hours Tracking Table].[Volunteer ID],
(Sum((DateDiff("n",[Start Time],[End Time])/60)*[Number of People])) AS Total
FROM [Volunteer Hours Tracking Table]
WHERE
((([Volunteer Hours Tracking Table].[Date Worked])>DateAdd("yyyy",-1,Date())))
GROUP BY [Volunteer Hours Tracking Table].[Volunteer ID];

if you want the sums to be just over volunteerID.