Inserting and Processing Data from one table to another table

[Lance1578]

Im trying to process the data from tblTempData to tblPData using the ff sql:

INSERT INTO tblPData ( fldMacID, fldYear, fldMonth, fldDay,fldTimeIn1,fldTimeOut1,fldTimeIn2,fldTimeOut2 )
SELECT 
 T1.fldMachineId, Year( T1.theDate),Month( T1.theDate),Day( T1.theDate),
 T2.fldLogDate - int(T2.fldLogDate) as TimeIn1,
 T3.fldLogDate - int(T3.fldLogDate) as TimeOut1,
 T4.fldLogDate - int(T4.fldLogDate) as TimeIn2,
 T5.fldLogDate - int(T5.fldLogDate) as TimeOut2
FROM (((
(Select Distinct tblTempData.fldMachineId,int(tblTempData.fldLogDate)  as theDate from tblTempData) as T1
    LEFT JOIN tblTempData as T2 ON (T1.fldMachineId = T2.fldMachineId AND T1.theDate = Int(T2.flgLogDate) AND T2.LogStatus = 1 AND Hour(T2.fldTimeLog) < 11)     )
    LEFT JOIN tblTempData as T3 ON (T1.fldMachineId = T3.fldMachineId AND T1.theDate = Int(T3.flgLogDate) AND T3.LogStatus = 2 AND Hour(T3.fldTimeLog) < 15)     )
    LEFT JOIN tblTempData as T4 ON (T1.fldMachineId = T4.fldMachineId AND T1.theDate = Int(T4.flgLogDate) AND T4.LogStatus = 1 AND Hour(T4.fldTimeLog) >= 11)     )
    LEFT JOIN tblTempData as T5 ON (T1.fldMachineId = T5.fldMachineId AND T1.theDate = Int(T5.flgLogDate) AND T5.LogStatus = 2 AND Hour(T5.fldTimeLog) >= 15)

When I run that code in MS Access Query Tools it didn't work as expected. And I cant really get it right.
I really appreciate any help on this.


Please see image for more info on my table and fields


Thank you very much.

[Micron]

Don't know if there's a sql guru lurking here that has a solution. Based on my interpretation I would use code, but I don't get why machine 2 on 9/20 with only 2 times isn't time1in and time1out.

[Lance1578]

Don't know if there's a sql guru lurking here that has a solution. Based on my interpretation I would use code, but I don't get why machine 2 on 9/20 with only 2 times isn't time1in and time1out.

Hi:
User 2 did not timeout1 and TimeIn2. He only TimeIn in the morning n TimeOut in the afternoon.

[June7]

Did you test the nested SELECT as a standalone query object? are the expected records retrieved?

[Micron]

At first I figured that 1 is IN and 2 is OUT, and if one occurs before noon it's Time1 but is Time2 if it's after noon. However, 9/19 doesn't support that theory so I'm not grasping the pattern.

[Lance1578]

The data comes from the biometric machine with the following format:
2 2017-7-20 7:05:00 1 0
2 2017-7-20 11:25:00 1 1
2 2017-7-20 12:30:00 1 0
2 2017-7-20 17:45:00 1 1
.....
almost 4k lines per month

LogStatus (TimeIn) = 1 (whether it is TimeIN1 or TimeIN2) once the user login it will mark 1
LogStatus (TimeOut) = 2 (whether it is TimeOUT1 or TimeOUT2) once the user logOut it will mark 2

ex1:
MacID | Year | Month | Day | TimeIn1 | TimeOUT1 | TimeIN2 | TimeOUT2
2 2017 7 20 7:05 11:25 12:30 17:45
2 2017 7 21 6:59 13:03 17:20
2 2017 7 22 11:45 17:56
2 2017 7 23 12:01

Sometimes the user forgot to login or logout so the field is blank.

Using this code ill be able to collect all TimeIn and TimeOut.. Sadly it takes me 4 Tables

// Insert to tblTimeIN1
INSERT INTO tblTimeIN1 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeIn1 )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate,tblTempData.fldLogDate, tblTempData.fldLogDate
FROM tblTempData
WHERE (((Format([tblTempData].[fldLogDate],"Short Time"))<"11:00")and tbltempdata.fldLogStatus = 1))
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);

// Insert to tblTimeOut1
INSERT INTO tblTimeOut1 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeOut1, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE Format([tblTempData].[fldLogDate],"hh:mm") >"11:00" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00" and tbltempdata.fldLogStatus = 2
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);

// Insert to tblTimeIN2
INSERT INTO tblTimeIN2 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeIn2, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE Format([tblTempData].[fldLogDate],"hh:mm") >"11:30" and Format([tblTempData].[fldLogDate],"hh:mm") < "14:00" and tbltempdata.fldLogStatus = 1
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);

// Insert to tblTimeOut2
INSERT INTO tblTimeOut2 ( fldMacID, fldYear, fldMonth, fldDay, fldTimeOut2, fldLogStatus )
SELECT tblTempData.fldMachineId, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogDate, tblTempData.fldLogStatus
FROM tblTempData
WHERE (((Format([tblTempData].[fldLogDate],"Short Time"))>"16:00" and tbltempdata.fldLogStatus = 2))
ORDER BY tblTempData.fldMachineId, Day(tblTempData.fldLogDate);

and have another table to collect all TimeIN1, TimeOUT1, TimeIN2, TimeOUT2.
as much as possible i would like to build all process in sql side.


Lance

[aytee111]

Your incoming data does not specify whether it is time 1, time 2, etc. I would highly recommend that you do not do that. Create one record per time in/out, regardless how many there are for a date. Does the incoming data not allow someone to do more than 2 logs per day? It sounds very risky to me.

As Micron says, so easy to do in code. Let us know if you need help with that.

[Micron]

so easy to do in code.

Except I'd still have no idea where to put which times because it seems arbitrary (per posts 2 and 5)

(whether it is TimeIN1 or TimeIN2);(whether it is TimeOUT1 or TimeOUT2)

Flip a coin?

[Lance1578]

Thanks for the suggestion. I keep you update.