Creating a DSum calculation in a query.

[tlang]

I am tracking staff hours worked in the field using Access. The Form I use to
input trelvanyt date is called frmWorkedEvent, based on tblWorkedEvent. Here
is the string I used to calculate the sum:
DSum("[TotalHoursWorked]","WorkedEvent","TimeType"), but here is the eror
message I get when trying to run this query:
"The Microsoft Jet database engine cannot find the input table or query
'WorkedEvent'. Make sure it exists and that it is spelled correctly."

The table most definitiely exists since I created the query using this
table. What gives?

 

[John Spencer MVP]

I'm confused.

You say frmWorkEvent is using tblWorkedEvent as its source. Then you use
WORKEDEvent in the DSum expression. So where do you have WorkedEvent as a
table or query?



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[tlang]

Actually, if you look back at my original post, you'll see I use
frmWorkedEvent (as in an event that has been staffed and worked in the past
and that I am tracking working hours for). The form was created by using the
AutoForm function after creating tblWorkedEvent to keep track of the relevant
data I need. I tried to create a Query using tblWorkedEvent and tblEmployee
(the second table was there so I could indicate which office the employee
works at). But when I write the DSum expression, Access tells me it can't
find tblWorkedEvent, even though that's the very table I used to create the
query in the first place.

I'm confused.

You say frmWorkEvent is using tblWorkedEvent as its source. Then you use
WORKEDEvent in the DSum expression. So where do you have WorkedEvent as a
table or query?



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I am tracking staff hours worked in the field using Access. The Form I use to
input trelvanyt date is called frmWorkedEvent, based on tblWorkedEvent. Here
is the string I used to calculate the sum:
DSum("[TotalHoursWorked]","WorkedEvent","TimeType"), but here is the eror
message I get when trying to run this query:
"The Microsoft Jet database engine cannot find the input table or query
'WorkedEvent'. Make sure it exists and that it is spelled correctly."

The table most definitiely exists since I created the query using this
table. What gives?

 

[John Spencer]

Your original post said that the expression you were using was

DSum("[TotalHoursWorked]","WorkedEvent","TimeType")

and the error message was
"The Microsoft Jet database engine cannot find the input table or query
'WorkedEvent'

WorkedEvent is not the same as tblWorkedEvent and that is why I am confused.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[tlang]

Okay. The table is tblWorkedEvent. According to Access for Dummies, a DSum
expression is created as follows: DSum(expression, domain, criteria). Replace
expression with the field name that you want to total, in quotes; replace
domain with the table or query name, in quotes; and optionally, include a
criteria that limits which records to include.

Thus, DSum("TotalHoursWorked","WorkedEvent","TimeType")

Should give me the total hours worked from the tblWorkedEvent, according to
the specified criteria in the TimeType field from the tblWorkedEvent.

I have successfully created strings like this in the past, but I have never
come across the particular error message I am getting now.

Is it a matter of naming the second element of the string "tblWorkedEvent"?
I simply cannot understand.

Your original post said that the expression you were using was

DSum("[TotalHoursWorked]","WorkedEvent","TimeType")

and the error message was
"The Microsoft Jet database engine cannot find the input table or query
'WorkedEvent'

WorkedEvent is not the same as tblWorkedEvent and that is why I am confused.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Actually, if you look back at my original post, you'll see I use
frmWorkedEvent (as in an event that has been staffed and worked in the past
and that I am tracking working hours for). The form was created by using the
AutoForm function after creating tblWorkedEvent to keep track of the relevant
data I need. I tried to create a Query using tblWorkedEvent and tblEmployee
(the second table was there so I could indicate which office the employee
works at). But when I write the DSum expression, Access tells me it can't
find tblWorkedEvent, even though that's the very table I used to create the
query in the first place.

 

[John W. Vinson]

Okay. The table is tblWorkedEvent. According to Access for Dummies, a DSum
expression is created as follows: DSum(expression, domain, criteria). Replace
expression with the field name that you want to total, in quotes; replace
domain with the table or query name, in quotes; and optionally, include a
criteria that limits which records to include.

Thus, DSum("TotalHoursWorked","WorkedEvent","TimeType")

Should give me the total hours worked from the tblWorkedEvent, according to
the specified criteria in the TimeType field from the tblWorkedEvent.

I have successfully created strings like this in the past, but I have never
come across the particular error message I am getting now.

Is it a matter of naming the second element of the string "tblWorkedEvent"?
I simply cannot understand.

If the table is named tblWorkedEvent then the second argument should be
"tblWorkedEvent". The prefix tbl is *not* meaningful to Access or to VBA, it's
just for the programmer's convenience. The second argument must be *the actual
name of the table*, whatever that is.

More critically, the third argument must be a valid SQL query WHERE clause
without the word "WHERE". "TimeType" does not qualify. Do you mean that the
Timetype field in your table contains valid SQL WHERE clauses? If not, what is
in TimeType and how do you decide which TimeType to use in the DSum?

 

[tlang]

I just want to know how to fix the problem. What do I write in the space
where the all the arguments go? I really don't get it and hope somebody can
just give me the answer please.

 

[John W. Vinson]

I just want to know how to fix the problem. What do I write in the space
where the all the arguments go? I really don't get it and hope somebody can
just give me the answer please.

We'll try but you'll have to give us some help. WE CANNOT SEE YOUR SCREEN.

What is the name of the field that you want to sum? (that's the first
argument)

What is the name of the table containing that field? (that's the second)

What criteria do you want to apply to select which records to sum?

 

[tlang]

The name of the field I want to sum is TotalHoursWorked

The name of the table containing that field is tblWorkedEvent

As for criteria, the closest I have come to a solution is by creating two
queries: the first sums the TotalHoursWorked field from tblWorkedEvent,
where the TimeType criteria is "PO" (paid overtime); the second query does
the same thing, but the TimeType criteria is "StraightTime".

This results in two queries that give me the information I want, but I want
to consolidate this into one query, where the TotalHoursWorked--both for
StraightTime and PO--are calculated separatley and appear in the same query.

So to restate, the criteria for TimeType of TotalHoursWorked is either PO or
StraightTime....Ultimately I would like to create a report from this query
that can display an employee's TotalHoursWorked at any given event is
calcualted in itemized fashion according to the TimeType, in order to
differentiate whether the employee will be paid overtime wages, StraightTime
wages, or both.