Help - possible re-structure - wonky data output (1 Viewer)

[Aryzona]

I have a form that employees fill out to track their work. I need to be able to get the total time they worked, the detail of what case numbers they processed and a count of how they were processed.

Easy enough, right? NOT ! If they do not close and reopen the form for the day ... works like a charm but....

It is conceivable they will close their form to go to lunch and then reopen the form when they come back ... this generates a 2nd ID for the days entry. This means that i now have 2 records for the date for the employee. so if they work 2 case numbers on session 1 and then work 2 case numbers on session 2 and put an hour in at each session ... their total time for the day should be 2 hours. however what I get is an hour attached to each case number processed so the total time comes out as 2 hours per session rolling up to 4 hours.

I have a table that records id, name, date, TCM, and time id. A table that records time id, hours, min. And a table that records the details, Case # , upload, utl, problem.

How do you suggest i structure my database to allow me to record:

Record ID#4 Name: Goofy Dawg Date: 1/8/21 TCM: true Total Time: 3:40 Total Processed: 5

Id# 1 123456 upload (time this session 1:40)
Id#1 1123456 problem
********(logout ... log back into form)**********
Id#2 3333333 UTL (time this session 2:00)
id#2 888888 upload
id#2 999444 upload

The master record is linked to the time record by the key field undmtid. The detail record is linked to the master by undmid. The undmtid generates a record recording the time for each time the form was used. So if i query just this table and total i get the total 3:40 but when I create a report grouped by name i end up with 17 hours as the total (3:40 x 5 case numbers processed).

Help! it has to be structural .. i just don't know how!

 

[Pat Hartman]

Reports work the same way as forms. Use a master for the 1-side record and a sub for the many-side records. That will allow you to summarize correctly. The start/end time belongs on the detail record BTW, not the master.

 

[plog]

It's best to work backwards, so show us what you want to end with. Don't just iterate the things you want on the report, mock up an example and show us.

Also, can you either post a copy of your database or a screenshot of completed Relationship Tool with all tables expanded to show their fields?

 

[Aryzona]

Reports work the same way as forms. Use a master for the 1-side record and a sub for the many-side records. That will allow you to summarize correctly. The start/end time belongs on the detail record BTW, not the master.

it isn't a start time / end time .. it is a total time. The employees are supposed to track how many hours / min they did this particular body of work ... so .. the form has them record their TIME as in .... i did this for 4 hours and 30 min. today.

The time itself should be a one to many - for 4 hours and 30 minutes today - i did 300 case#'s.

I do not have the time in the master table or the detail table. It is stored in a separate table - undmtID, undmt_hr, undmt_m and the master table has the key field undmtid as the relationship connection to the time record, and the master table has the undmdID as the relationship connection to the detail tables key undmd_ID.

The reason i did not put the time in the detail .. is there will be 100 detail records (processed case #'s ) to 1 - employee on this date. I need to record the amount of time they spend doing the 100's of detail records with out having them document the start / stop for each individual processed case. If i put the time in the detail they will have to record the time spent on each individual case #.

Do i need to add a field in the detail that autofills time for each line ? so i would have 300 records with say 1 minute each? then i could total that, and display the sum as Total Hours: 5:00 in the report header. and this would be Date Diff?

They currently record this information on a spreadsheet which is then entered into a different database so ... there is a definitive time. This system is labor intensive in that ... they must email that spreadsheet daily to a manager, who hands it off to another employee to do the entry in the database. The intent is to allow the employees to track their own time by mimicking the spreadsheet in a data entry form.

I have it mimicked perfectly and it works perfectly IF they leave the form open until they are done. It is not uncommon to switch from one body of work according to priority and then come back to what you were doing before.... so i anticipate they will close the form and then come back and continue working. This is where i run into trouble.

 

[Aryzona]

It's best to work backwards, so show us what you want to end with. Don't just iterate the things you want on the report, mock up an example and show us.

Also, can you either post a copy of your database or a screenshot of completed Relationship Tool with all tables expanded to show their fields?

the report outcome is what i need.

Employee Name Date Total Time Spent (currently employees declare i spent 4 hrs and 30 min today ) Total Processed: 300

the detail is
Case # Uploaded
Case # UTL (unable to locate)
Case # Problems
X300 or so...... the upload, UTL and Problems are currently check boxes... they just check the box that applies to the case # and i do a "Count " to get the total processed.

We make them document each case number as .. proof? .. of what was done during the time span declared.

 

[Cronk]

Seems to me you have 2 basic entities, employees and jobs. One table is used for each entity. You record the times worked for each employee. You record separately each job that the employee completes and the time completed.

Then you can report on the number of jobs completed by each employee in any time period.

 

[Aryzona]

correct ... but there are other jobs and other types of tracking that occur. so the Employee file is just an ID# field their name_txt. The individual types of tracking and jobs are all in a unique file ... undm - fs- rsci - etc.... the connection is the employee id in each of the jobs/tracking tables. Each job / tracking table has a unique (auto number) id field for the record and the employee id that record belongs to.

 

[Cronk]

If you have a table of jobs with the EmployeeID who worked on them and the date each job was finalized, then for any date you can list against each employee how many jobs were finished. Alternatively, you can list for a particular employee how many jobs were completed each day for any period.

Perhaps you should post your database, indicating which form is problematic for you.

 

[Pat Hartman]

If you are putting the time on the employee level and you have a child table, then when you join the two tables, you get a row for every row in the child table so if an employee has three dependent records, his time is being multiplied by 3 when you sum it. Do what I said - create a subreport for the many-side data and this problem goes away.

Also, don't store the time as a date time field. Store it as two fields - hours and minutes. Datetime fields are meant to store a point in time, NOT elapsed time. If you sum the hours worked, once you get over 24 hours, it will roll the day. So, if you are only showing hh:nn, and you add 3:30 to the sum of 22:00, your result will be the next day (12/31/1899) and 1:30 as the time.