|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
February 18th, 2007, 11:35 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Expression on a report
I have an unbound text box with the expression =Sum([Employee Regular Time]).
I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated. 
|
|
#2
February 19th, 2007, 12:52 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
You can total a subset of records with a general expression like:
=Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
|
#3
February 19th, 2007, 03:11 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
I tried it in a text box control source and it brought back an error? any
other ideas? "Duane Hookom" wrote: You can total a subset of records with a general expression like: =Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
|
#4
February 19th, 2007, 05:45 AM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
Tried what? What was the Control Source? What section of the report? "brought
back an error"... what error? -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I tried it in a text box control source and it brought back an error? any other ideas? "Duane Hookom" wrote: You can total a subset of records with a general expression like: =Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
|
#5
February 19th, 2007, 02:14 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text
box control source on the Reports Page Footer. When I ran the report It asked me for a Date like it is supposed to and when the report came up the text box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error. There was no pop up or anything like that it just said error in the text box. "Duane Hookom" wrote: Tried what? What was the Control Source? What section of the report? "brought back an error"... what error? -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I tried it in a text box control source and it brought back an error? any other ideas? "Duane Hookom" wrote: You can total a subset of records with a general expression like: =Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
|
#6
February 19th, 2007, 02:41 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
The text box needs to be in a Report or Group Header or Footer section. You
can't easily aggregate records in Page sections. -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text box control source on the Reports Page Footer. When I ran the report It asked me for a Date like it is supposed to and when the report came up the text box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error. There was no pop up or anything like that it just said error in the text box. "Duane Hookom" wrote: Tried what? What was the Control Source? What section of the report? "brought back an error"... what error? -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I tried it in a text box control source and it brought back an error? any other ideas? "Duane Hookom" wrote: You can total a subset of records with a general expression like: =Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
|
#7
February 19th, 2007, 03:17 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Expression on a report
Thanks a million!!! I was putting it in the wrong spot!!!!
"Duane Hookom" wrote: The text box needs to be in a Report or Group Header or Footer section. You can't easily aggregate records in Page sections. -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: Ok, I tried adding =Sum(Abs([Shift]=1) * [Employee Regular Time]) into a text box control source on the Reports Page Footer. When I ran the report It asked me for a Date like it is supposed to and when the report came up the text box with the "=Sum(Abs([Shift]=1) * [Employee Regular Time])" gave an error. There was no pop up or anything like that it just said error in the text box. "Duane Hookom" wrote: Tried what? What was the Control Source? What section of the report? "brought back an error"... what error? -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I tried it in a text box control source and it brought back an error? any other ideas? "Duane Hookom" wrote: You can total a subset of records with a general expression like: =Sum(Abs(..True/FalseExpression..) * [NumericFieldToSum]) In your case, this would be =Sum(Abs([Shift]=1) * [Employee Regular Time]) -- Duane Hookom Microsoft Access MVP "oxicottin" wrote: I have an unbound text box with the expression =Sum([Employee Regular Time]). I want to categorize by text box [Shift]. For example, the sum of shift 1 is 12 hours, shift 2 is 24 hours, shift 3 is 18 hours. Can I have an unbound text box show the sum of each Employee Regular Time by shift. I tried writing a query that was bound to my initial query. The only problem is that there was criteria place in the initial query. When I got to the report, The report asked for Date 4 times (I believe because of initial query and then 3 shifts.) Any help would be appreciated.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
