select or dsum???

  • Thread starter Thread starter Maresdd
  • Start date Start date
M

Maresdd

I have a query based on hours that employees work. The table fields include
Carer,ServiceDate,Hours,Category,Servicetype. What I need to calculate is if
an employee works more than 2 hours in say Category 3 for the day, then the
rest of categories need to be totalled for that carer for that 1 day. I've
tried select statements and dsum. Can someone point me in the right direction
please.

This is what I have tried.

Cat: IIf(DSum("[Hrs]","[awardquery]","Carer = Carer AND ServiceDate =
ServiceDate")>2,3,awardquery!Category)

I end up with every shift being a category 3. How do I set the criteria to
only look at the table where the Carer is the same and the Servicedate is the
same?
 
hi,

I have a query based on hours that employees work. The table fields include
Carer,ServiceDate,Hours,Category,Servicetype. What I need to calculate is if
an employee works more than 2 hours in say Category 3 for the day, then the
rest of categories need to be totalled for that carer for that 1 day. I've
tried select statements and dsum. Can someone point me in the right direction
please.
Click to expand...
I'm not sure, if I understand you correctly, but..

This is what I have tried.

Cat: IIf(DSum("[Hrs]","[awardquery]","Carer = Carer AND ServiceDate =
ServiceDate")>2,3,awardquery!Category)
Click to expand...
You need to change your DSum() condition to reference the actual row values:

DSum("[Hrs]",
"[awardquery]",
"[Carer] = " & [Carer] & " AND [ServiceDate] = " & ServiceDate)

You alse need some string formatting:

DSum("[Hrs]",
"[awardquery]",
"[Carer] = '" & Replace([Carer], "'", "''" & "'"
" AND [ServiceDate] = " &
Format([ServiceDate], "\#m\/d\/yyyy hh\:nn\:ss\#"))

The first formatting with Carer is necessary if this value is stored as
a text field.

The second formatting with ServiceDate is necessary if this value is
stored as Date/Time.


mfG
--> stefan <--
 
thank you Stefan.

I kept working on it after I posted last night and I came up with:

Cat: IIf(DSum("[Hrs]","[awardquery]","[Category] = 3 and
[awardquery].[carer]=""" & awardquery.carer & """ AND ServiceDate = #" &
[ServiceDate] & "#")>2,3,[Category])

The only thing is that the query takes forever.

I tried your suggestion but I keep getting a syntax error.

This is with your suggestion:
Cat: IIf(DSum("[Hrs]",
"[awardquery]",
"[Carer] = '" & [Carer], "'", "''" & "'"
" AND [ServiceDate] = " &
Format([ServiceDate], "\#m\/d\/yyyy#")) >2,3,[Category])






Stefan Hoffmann said:
hi,

I have a query based on hours that employees work. The table fields include
Carer,ServiceDate,Hours,Category,Servicetype. What I need to calculate is if
an employee works more than 2 hours in say Category 3 for the day, then the
rest of categories need to be totalled for that carer for that 1 day. I've
tried select statements and dsum. Can someone point me in the right direction
please.
Click to expand...
I'm not sure, if I understand you correctly, but..

This is what I have tried.

Cat: IIf(DSum("[Hrs]","[awardquery]","Carer = Carer AND ServiceDate =
ServiceDate")>2,3,awardquery!Category)
Click to expand...
You need to change your DSum() condition to reference the actual row values:

DSum("[Hrs]",
"[awardquery]",
"[Carer] = " & [Carer] & " AND [ServiceDate] = " & ServiceDate)

You alse need some string formatting:

DSum("[Hrs]",
"[awardquery]",
"[Carer] = '" & Replace([Carer], "'", "''" & "'"
" AND [ServiceDate] = " &
Format([ServiceDate], "\#m\/d\/yyyy hh\:nn\:ss\#"))

The first formatting with Carer is necessary if this value is stored as
a text field.

The second formatting with ServiceDate is necessary if this value is
stored as Date/Time.


mfG
--> stefan <--
.
Click to expand...
 
thank you Stefan.

I kept working on it after I posted last night and I came up with:

Cat: IIf(DSum("[Hrs]","[awardquery]","[Category] = 3 and
[awardquery].[carer]="""& awardquery.carer& """ AND ServiceDate = #"&
[ServiceDate]& "#")>2,3,[Category])

The only thing is that the query takes forever.
Click to expand...
You must ensure that the tables used by [awardquery] have non-unique
indices on [carer] and [ServiceDate]. If both fields are stored in the
same table, create on combined, non-unique index.
I tried your suggestion but I keep getting a syntax error.

This is with your suggestion:
Cat: IIf(DSum("[Hrs]",
"[awardquery]",
"[Carer] = '"& [Carer], "'", "''"& "'"
" AND [ServiceDate] = "&
Format([ServiceDate], "\#m\/d\/yyyy#"))>2,3,[Category])
Click to expand...
You need to use the Replace() function correctly, take a look at my
previous answer.


mfG
--> stefan <--
 
Back
Top