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
|
|||
|
|||
One fourth of running total
I have a database to keep track of training time. It
contains an Employees table, a Training Session table (for subject, instructor, hours, etc.), and an Enrollment table as a junction table. There is a many-to-many relationship between Employee and Session (each employee can attend many sessions, each session can include many employees), thus the junction table, which contains employee name and date. A report summarizes the information for each employee. It is based on a parameter query that includes the three tables mentioned above, and is grouped by employee. There is a text box for Training Time (from the Training Session table), with its running sum set to Over Group. This works as intended to total the training time for each employee. However, some training is on-the-job (OJT), which counts as one quarter of the the time recorded. Eight hours of OJT = 2 hours of training. What I need to do is to add regular (not OJT) training plus one quarter of OJT, and to have a running sum on the report. How can I do this? |
#2
|
|||
|
|||
One fourth of running total
Without having more specififc data I would suggest to use
IIF([TraningType] = "OJT",[TrainingTime]/4,[TrainingTime]) this IIF statement would devide the time by 4 if it is OJT and otherwise will return the full time. Hope this helps. Fons -----Original Message----- I have a database to keep track of training time. It contains an Employees table, a Training Session table (for subject, instructor, hours, etc.), and an Enrollment table as a junction table. There is a many-to-many relationship between Employee and Session (each employee can attend many sessions, each session can include many employees), thus the junction table, which contains employee name and date. A report summarizes the information for each employee. It is based on a parameter query that includes the three tables mentioned above, and is grouped by employee. There is a text box for Training Time (from the Training Session table), with its running sum set to Over Group. This works as intended to total the training time for each employee. However, some training is on-the-job (OJT), which counts as one quarter of the the time recorded. Eight hours of OJT = 2 hours of training. What I need to do is to add regular (not OJT) training plus one quarter of OJT, and to have a running sum on the report. How can I do this? . |
#3
|
|||
|
|||
One fourth of running total
Thanks for your reply. You got me on track, although I'm
not sure if it was as you intended. I used a variant of your formula as a calculated field [OJTcalculated] in the report's underlying query. The variation is that [OJT] is a Yes/No field, so it was = IIf([OJT] = True, etc.). I added a text box (txtOJTcalculated) to the report with its control source set to [OJTcalculated]. I made it invisible, and set its running sum to Over Group (the report is grouped by employee). The group footer contains an unbound text box set to = [txtOJTcalculated] & "Hours". The difficulty I was having before was that I was attempting to perform the calculation on the running sum rather than on the individual training session. -----Original Message----- Without having more specififc data I would suggest to use IIF([TraningType] = "OJT",[TrainingTime]/4,[TrainingTime]) this IIF statement would devide the time by 4 if it is OJT and otherwise will return the full time. Hope this helps. Fons -----Original Message----- I have a database to keep track of training time. It contains an Employees table, a Training Session table (for subject, instructor, hours, etc.), and an Enrollment table as a junction table. There is a many-to-many relationship between Employee and Session (each employee can attend many sessions, each session can include many employees), thus the junction table, which contains employee name and date. A report summarizes the information for each employee. It is based on a parameter query that includes the three tables mentioned above, and is grouped by employee. There is a text box for Training Time (from the Training Session table), with its running sum set to Over Group. This works as intended to total the training time for each employee. However, some training is on-the-job (OJT), which counts as one quarter of the the time recorded. Eight hours of OJT = 2 hours of training. What I need to do is to add regular (not OJT) training plus one quarter of OJT, and to have a running sum on the report. How can I do this? . . |
#4
|
|||
|
|||
One fourth of running total
Glad it worked out.
Fons -----Original Message----- Thanks for your reply. You got me on track, although I'm not sure if it was as you intended. I used a variant of your formula as a calculated field [OJTcalculated] in the report's underlying query. The variation is that [OJT] is a Yes/No field, so it was = IIf([OJT] = True, etc.). I added a text box (txtOJTcalculated) to the report with its control source set to [OJTcalculated]. I made it invisible, and set its running sum to Over Group (the report is grouped by employee). The group footer contains an unbound text box set to = [txtOJTcalculated] & "Hours". The difficulty I was having before was that I was attempting to perform the calculation on the running sum rather than on the individual training session. -----Original Message----- Without having more specififc data I would suggest to use IIF([TraningType] = "OJT",[TrainingTime]/4, [TrainingTime]) this IIF statement would devide the time by 4 if it is OJT and otherwise will return the full time. Hope this helps. Fons -----Original Message----- I have a database to keep track of training time. It contains an Employees table, a Training Session table (for subject, instructor, hours, etc.), and an Enrollment table as a junction table. There is a many-to-many relationship between Employee and Session (each employee can attend many sessions, each session can include many employees), thus the junction table, which contains employee name and date. A report summarizes the information for each employee. It is based on a parameter query that includes the three tables mentioned above, and is grouped by employee. There is a text box for Training Time (from the Training Session table), with its running sum set to Over Group. This works as intended to total the training time for each employee. However, some training is on-the-job (OJT), which counts as one quarter of the the time recorded. Eight hours of OJT = 2 hours of training. What I need to do is to add regular (not OJT) training plus one quarter of OJT, and to have a running sum on the report. How can I do this? . . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Running sum used in total calculation? | Louise54 | Setting Up & Running Reports | 1 | June 29th, 2004 04:41 PM |
Running Total | Kristen | Using Forms | 3 | June 28th, 2004 09:21 PM |
calculate a running total | Rick | Setting up and Configuration | 1 | March 30th, 2004 12:17 PM |
Searching a column multiple times for a value / returning a running total of values | Jeff King | Worksheet Functions | 0 | December 8th, 2003 05:17 PM |
Running Total Chart question | Neil Grantham | Charts and Charting | 6 | October 26th, 2003 04:03 PM |