Job log and time stamps for records

[mooreb55020]

Hello All,

Thanks in advance for helping me out!

I'm trying to find a way to develop a log per job order where the operator will scan in their job number, their employee ID and their machine efficiency when they punch into the job. Once they hit save I would like for it to record the time they punched in. then when they punch out to break i'd like it to record pieces produced and pieces scrapped on the same entry as before but this time record the time they punched out. Is there a way to do this with 2 separate forms or can I create panels. I'm not overly familiar with Access I'm more familiar with Visual Studio and SQL Server however, the company I work for doesn't want to commit to that software.

Thanks

[CJ_London]

Is there a way to do this with 2 separate forms or can I create panels

you can do either. How you do it is another matter. If you are used to using visual studio/sql server the principles will be the same - a table for logging transactions, code to manage whether they are logging in or out (and for what reason - break/finished job/end of day etc), a form or forms to capture the required information.

You need to think through the practical application of the requirement - does each employee have their own device? or do they go to some central point. How the scanner integrates with the rest of the business, etc

they hit save I would like for it to record the time they punched in.

use a datetime field with the default set to now()

[mooreb55020]

Ajax

so the employees will be scanning at a central location around the plant floor (we are setting up 4 of them for the time being until I can prove out that each work center needs one) the goal is when they log in they will use 1 form which will record their time in, the job number, the operator employee id (name badge has been given to all employees) and then their machine performance base on first cycle of the session. when they go to break they will need to scan out of the job and into break which then a break giver will scan into the job. so i'm looking for it to go through the table find the last occurrence where the job number and the operator ID match what's in the controls on the form. this is due to we can have multiple employees at a work center at any given time and they may need to be moved around to reduce bottlenecks. i have the punch in one completed my struggle is getting the punch out to find that record and save the production data on that row.

[CJ_London]

i have the punch in one completed my struggle is getting the punch out to find that record and save the production data on that row.

this is excel thinking, not database thinking.

each punch creates a record, as a minimum it stores the employeeID and the date/time. You will probably have other fields such as machine/line, production data, job number and 'direction' (in/out). table might look something like


PK.....EmpID….In/Out....JobNo...clockTime
1...….10...……..I...………..0...……..08:00
2...….12...……..I...………..0...……..08:01
3...….10...……..I...………..123...….08:10
4...….10...……..O...……….123...….08:15
5...….12...……..I...………..123...….08:16
6...….10...……..O...……….0...……..08:20
7...….10...……..I...………..0...……..08:25


This tells the story that employee10 started work at 8am, at 08:10 he starts on job 123 and finishes it at 8:15, at 8:20 he goes on a break returning at 08:25. In the meantime employee 12 arrived at work just after employee10 and sat around until 08:16 when he took over job 123 - and he's still working on it. You can use queries to determine how long the employee spent on a job, how long a job took across different employees, etc.

You may need more fields to indicate start/end of day, coffee break v lunch break etc. Perhaps also something that explains what they were doing between 8:15 and 8:20

You could change the above to have a intime and an outtime without the in/out flag which is what I think you are trying to achieve but I don't recommend it. I've built a number of time recording systems over the years and the above technique has much fewer issues overall.

However if you want to go your route then to find the relevant record the query would be something like

SELECT *
FROM tblTimes
WHERE EmpID=[Enter EmpID] and JobNo=[Enter JobNo] and outTime is null

The issue in both cases is ensuring that the employee 'does it right' - i.e. does not forget to clock in or out and does so at the appropriate time and not 2 hours later. Also checks to make sure they select the right job, and they don't have someone else clocking in or out on their behalf.

[mooreb55020]

You are brilliant! I thumped my head against the desk trying to outsmart the system I built in excel and built something similar in MySQL so I thought I could do it. Appreciate the help and I’ll try to build it the way you recommend above and figure out the query and how to calculate time in and time out.

[CJ_London]

time is stored as a decimal number - consequently for a simple situation of just two records you can treat it as a number and add and subtract i.e. use an aggregate query

Unlikely to be useful, but just as an illustration using my example - records 3 and 4

SELECT EmpID, JobNo, sum(iif([in/out]="I",-1,1)*clocktime) as ttlTime
FROM tblTimes
WHERE EmpID=[Enter EmpID] and JobNo=[Enter JobNo]
GROUP BY EmpID, JobNo

However probably not worth using this method if the job is broken up with breaks etc

Otherwise it is case of finding the two records. To do this reference the table twice and link

SELECT * FROM
tblTimes AS inRec INNER JOIN tblTimes AS outRec ON inRec.EmpID=outRec.EmpID AND inRec.JobNo=outRec.JobNo
WHERE inRec.[in/out]="I" and outRec.[in/out]="O"

there are other ways, but just to illustrate the sort of query you need to create