An odd =Sum() question?

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


"John W. Vinson" wrote:

On Tue, 7 Oct 2008 11:03:03 -0700, Flopbot
wrote:

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 [MVP]

On Wed, 15 Oct 2008 15:28:07 -0700, Flopbot
wrote:

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 [MVP]

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.

"KARL DEWEY" wrote:

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


"Flopbot" wrote:

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.

"KARL DEWEY" wrote:

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


"Flopbot" wrote:

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

"KARL DEWEY" wrote:

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


"Flopbot" wrote:

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,

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 W. Vinson" wrote:

On Wed, 15 Oct 2008 15:28:07 -0700, Flopbot
wrote:

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 [MVP]

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 W. Vinson" wrote:

On Wed, 15 Oct 2008 15:28:07 -0700, Flopbot
wrote:

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 [MVP]

On Sat, 18 Oct 2008 08:11:01 -0700, Flopbot
wrote:

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 [MVP]

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 W. Vinson" wrote:

On Sat, 18 Oct 2008 08:11:01 -0700, Flopbot
wrote:

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 [MVP]

On Tue, 21 Oct 2008 04:59:01 -0700, Flopbot
wrote:

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 [MVP]

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];


"John W. Vinson" wrote:

On Tue, 21 Oct 2008 04:59:01 -0700, Flopbot
wrote:

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 [MVP]

On Tue, 21 Oct 2008 12:55:01 -0700, Flopbot
wrote:

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

John W. Vinson [MVP]