Query or Code - Can You help

[Ste4en]

Query or Code - Can You help pleeeese

I posted this question a few weeks ago and did not get much / any response probably because my Q was unclear – I have reworded it - sorry it is long but not complicated.

An entry scanner provides 2 lines of raw data per employee each day. Using a query I use the Max of punch for the Out time and Min of punch for the In time. This is done by a query.

01/20/02 JoeNightWorker; Shift2; Punch 240
01/20/02; JoeDayWorker; Shift1; Punch 360
01/20/02; JoeDayWorker; Shift1; Punch 960
01/20/02; JoeNightWorker; Shift2; Punch 1080
01/21/02; JoeNightWorker; Shift2; Punch 240

The dayshift is easy, using MaxofPunch and MinofPunch - JoeDayWorker entered 360 minutes after midnight on 01/20/02 (6am) and that he left 600 minutes later at 960 minutes after midnight (4pm) on 01/20/02.

Nightshift is my problem. JoeNightWorker arrived at 6pm, 01/20 and left the following morning at 4am, 01/21. Following the same rules as for shift 1 would provide that he clocked in at 4am and went home at 6pm. The time difference is correct but it is based on the wrong pair of punches as we would find out on Monday when there would be no out punch for the Sunday night when we don't work. MY problem is not calculating a time difference BUT camparing the right pair of punches.

My guess at a solution is as follows: When the shift = 2, use the Max of Punch of [ReportDate] as the InPunch and the Min of Punch of the [Report Date]+1 (tomorrow) as the OutPunch. This way I would be reading the correct In and Out Punch pair.

I can’t work this out in a query and so if I knew what I was doing I would write the following in real code and put it somewhere.

If shift = 1 then
PunchDate = ReportDate
InTime = MinofPunchTime
Outtime = MaxofPunchtime

else '(shift=2)
PunchDate = ReportDate
InTime =MaxOfPunchTime

PunchDate=ReportDate+1
OutTime=MinofPunchTime


Trouble is I don’t know how to write the code or where to put it or call it. Any help appreciated.


Many thanks.

 

[jdjewell]

My solution...

...do a search for "DateDiff" in this forum. If you are not familiar with this function, it can take two date/time stamps and compute the difference how you want to see it (which in this case is probably hours or minutes). At any rate, you will probably want to then change "Punch" to be a Date/Time field, and unless you need to know for some other reason, you would no longer need the Shift1, Shift2 stuff.

Let me know if this leaves things unresolved for you.

 

[Ste4en]

I agree that this would be the best way but unfortunately I don't have that sort of data. I get it as I showed. I think I would still need a way of linking the pair of punches. (Last Nights In and this mornings Out). My data also does tell me if the person scanned in or out; only that he scanned.

The method I suggested with the if-then-else code seems like it would work - is it not possible to do it like that?. Or is it just silly?

I only have a night shift for the next month so an ugly or silly way round this would be quite acceptable.

Any other ideas.

Thanks for helping.

Steve

 

[Jon K]

Attached is a DB, incorporating your five records. It contains two queries.

qryOne:-
SELECT PunchDate, Worker, Shift, PunchTime,
(Select Count(*) from tblPunchTime as S where Worker=tblPunchTime.Worker and Shift=tblPunchTime.Shift and PunchDate+CDate(PunchTime/(24*60)) <= tblPunchTime.PunchDate+CDate(tblPunchTime.PunchTime/(24*60))) AS Rank,
IIf([Shift]=1 And Rank Mod 2=0,"Out",IIf([Shift]=1 And Rank Mod 2<>0,"In", IIf([Shift]=2 And [PunchTime]>12*60,"In","Out"))) AS InOut
FROM tblPunchTime;

qryTwo:-
SELECT PunchDate AS Report_Date, Worker, Shift, PunchDate AS Punch_In, PunchTime AS In_Time,
CDate(IIf([Shift]=1,DLookUp("PunchDate","QryOne","PunchDate=#" & a.PunchDate & "# and Worker='" & [Worker] & "' and PunchTime >" & a.PunchTime),DLookUp("PunchDate","QryOne","PunchDate=#" & a.PunchDate+1 & "# and Worker='" & [Worker] & "'"))) AS Punch_Out,
CInt(IIf([Shift]=1,DLookUp("PunchTime","QryOne","PunchDate=#" & a.PunchDate & "# and Worker='" & [Worker] & "' and PunchTime >" & a.PunchTime),DLookUp("PunchTime","QryOne","PunchDate=#" & a.PunchDate+1 & "# and Worker='" & [Worker] & "'"))) AS Out_Time
FROM QryOne AS a
WHERE InOut="In";


The first query identifies each record as In or Out. For Shift=2, it uses the rule: [PunchTime]>12*60 is "In", otherwise "Out". (Note: The rule is not foolproof. It breaks if for some reason JoeNightWorker just has to leave before mid-night one particular evening.)

The second query selects only those "In" records from the first query and uses the DLookup() function to find the "Out" date and time.

Hope it helps.


(Note: The attached DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)

 

[Ste4en]

Jon, This looks good, I have tried my best to understand how it works but that will take a while. I tried some test data and it needs modification to take care of missed punches - i.e. where people forget to punch.

What I have been doing so far for the day shift is - if there is only one punch for a day I make the scan = to the other scan. OutScan = InScan. The time difference is 0.

The SQL you wrote - if there is no outpunch or inpunch for a shift then it gives the #error for the outpunch. Can the SQL be modified to make Outscan=Inscan if its an #error or something.

Thank you very much for your help.

Steve

 

[Jon K]

Could you attach a DB with the table containing the test data? It would be a lot easier for me as it could remove guesswork.

You can create a new DB. Import the table into it. Remove or change any sensitive data. Compact the DB. Zip it and attach it here.

 

[Ste4en]

Here is some test data.
I need to be sure that if there is only one punch per shift either in or out that the punch is still selected. If there is no in or out data the in or out can be equal.

Many thanks
Steve

 

[Jon K]

I examined the records for 28 of the workers (in the Excel file attached below).

The absence of some in-scans and out-scans, plus the fact that some worker would punch 3 times on a particular day, has thrown the records in the table out of pattern. As a query works on records in batch, pattern is important. The lack of a pattern in the records has rendered the query qryOne unable to accurately mark each record as In or Out. This in turn has made the query results of qryTwo erroneous.

Is it possible to have an indicator of In or Out added by the scanner? Or can the In and Out records be scanned to two different tables?

Without the indicator of in-scan or out-scan in the records, I don't think the problem can be resolved by means of queries or VBA code.

 

[Ste4en]

The way I have been calculating the day punches is by using the Max and Min of the punch information as I explained in my first post. I think I can use this for the night shift if I first subtract 12 hours or 720 minutes from all the 2nd shift night punches and then deal with it as a day punch. Then add the 720 minutes back for the final report.

Hopefully I will get this working this week and before the night shift ends.

THanks