• Registered: 2021-09-10
  • Posts: 6

Topic: Summing Specified rows in a report

Hello guys,

I work in the automotive industry and was trying to make a database to record and report the daily job performance of technicians and I came across a problem. I prepared the database without any coding knowledge so I need your help with this problem. the database is used to record the daily working activities of technicians with standardized operations each having its own standard time to perform. It was working perfectly when I suddenly found a problem with the exaggerated efficiency calculation on the report. Those came from standard times that are greater than 8hrs which could not be finished in a day. But my database's actual time can only calculate the actual time for 1 day only based on the inputs. I was trying to find a way to add the actual working time of technicians for each day which has the same Job numbers and operation codes and need your assistance on this. I've attached the google drive link to my database and thanks a lot.

shorturl.at/ijC48

2 Reply by derek (edited by derek 2021-09-11 00:18:33)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,761

Re: Summing Specified rows in a report

Hi,
Thanks for attaching your project - it makes things a lot easier when trying to offer suggestions.
I think the best way around your issue would be to split the existing 'jobs_done_detail' table into two tables
1.  'jobs_done_header' (with fields of job_no, job_date, total_time)
2.  'jobs_done_detail' (with a relationship up to 'jobs_done_header and the remaining fields that are in 'jobs_done_detail').
This would allow you to have a job that spanned any number of days (and also would allow more than one technician to work on the same job at the same time (I don't know if that happens) and/or different technicians spanning different days).
The total time spent on a job('jobs_done_header') would then be the sum of all the AM and PM times for all the related 'jobs_done_detail' records.
I noticed a couple of things on the 'jobs entry' form that you may not be aware of if you're new to MVD so I'll mention them just for info'
1.  in comboboxes, you can combine fields rather than just display one - so for Oper'n Code, you can see the code, the name, standard hours etc all in the combobox rather than using a separate grid as you were doing.  Also have a look at the Remark combobox.
2.  you can use the format property of datetimepicker fields to show how you want the date or time displayed - in your example, you probably only want to see hours and minutes.
3.  you can also change the display of times to just hours and minutes in the relevant tablegrid with a small bit of code (in the 'script' tab).
Please see the attached which may be of help.
Derek.

Post's attachments

Attachment icon Labour Guide App2.zip 1.14 mb, 348 downloads since 2021-09-11 

  • Registered: 2021-09-10
  • Posts: 6

Re: Summing Specified rows in a report

Thanks, a lot Derek, That was a very helpful suggestion you provided and I managed to group the Jobs_done_detail based on Job_No as shown in the file attached. But here also I need help, to sum up the STD_Times that I found from each group on my report to finalize the report summary section highlighted in RED. Knowing the sum will help me evaluate the rest of the quantities like the average efficiency and others. Thanks in advance.

Post's attachments

Attachment icon Labour Guide App2.zip 1.14 mb, 302 downloads since 2021-09-13 

4 Reply by derek (edited by derek 2021-09-14 20:01:25)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,761

Re: Summing Specified rows in a report

Hi,
I was thinking that you should just be able to sum the values of 'memo63' (which is the total standard working time for each group footer) - see screen 1 in the attachment. 
But for some reason, it never includes the final 'group footer' total in the calculation - see screen 2 in the attachment.
Whether this is because of the sequence in which it performs the calculations or something else, I'm not sure. 
I was surprised to see you had specified 'operation code' in the 'group header' rather than 'job_no' so that might be something to look at.
I don't use FastReport much so I'm not the best person to be asking - maybe someone else can give you some help.
Derek.

Post's attachments

Attachment icon standard time problem.zip 696.78 kb, 341 downloads since 2021-09-14 

5 Reply by birukyif (edited by birukyif 2021-09-15 10:23:38)

  • Registered: 2021-09-10
  • Posts: 6

Re: Summing Specified rows in a report

Hello,

Many thanks for your assistance Derek. I've got valuable tips and tricks from you on MVD. I have refined my report based on your previous suggestions but I still can't get the correct total STD time value and I don't know why. If anyone is out there who has better experience with Fastreport, please help me out on this.

Post's attachments

Attachment icon Labour Guide App2-Edited.zip 1.14 mb, 352 downloads since 2021-09-15