Qury running really slow

[mousemat]

Hi all

I have been making a T & A Database with data coming direct from the time clocks. Trouble is it puts each clockin on a seperate row.

I have been using the code I found on this forum posted by Jon K

Assuming DATE, TIME are date/time fields in table tblPunchCard, try these two queries (type/paste in the SQL View of each new query):

qryOne:-
SELECT [EMP ID], [DATE], [TIME], 
(Select count(*) from [tblPunchCard] where [EMP ID] = a.[EMP ID] and [Date]+[Time] <= a.[Date]+a.[Time]) AS Num, 
iif(Num mod 2 =1, "In" ,"Out") AS InOut, 
iif(InOut="In", Num, Num-1) AS Pair
FROM [tblPunchCard] AS a;


qryTwo:-
SELECT [EMP ID], Min([Date]) AS [Clock-In Date], 
format(Min([Date]+[Time]), 'Short time') AS [Clock-in Time],
iif(Max([Date]+[Time]) <> Min([Date]+[Time]), Max([Date]), Null) AS [Clock-out Date], 
iif(Max([Date]+[Time])<>Min([Date]+[Time]), format(Max([Date]+[Time]) ,'Short Time'), Null) AS [Clock-out Time]
FROM qryOne
GROUP BY [EMP ID], Pair;


Run the second query.

(As clock-in date and clock-out date are different in the sample data, I have included [Clock-out Date] in the query result. You can delete it if you want to.)
__________________
Access 2003, WinXP

This works great at putting clocks in the right order. The trouble I have is that it becomes really slow when using lots of data, normally there are 4 clockins per person per day, so on a company that has 70 exployees, thats 280 clockins per day. This really really slows the query down. Is there a way that I can speed things up??

 

[DCrake]

See this post I commented on earlier.

 

[mousemat]

Thats the problem. The software polls the data from the Clock Terminal into a SQL table, every clock transaction is entered into a new record. I cant change that.

However, having found the code above, it puts the records into pair and like I say it works well for a limited number of records, its when processing large number of records it takes an age.

I am looking into manipulating the data into one table at the moment and am sort of getting there.

I have a time zone table with 6 fields (Clockin Start, Clockin Finish, ClockOutLunch, ClockInLunch, ClockOut Start, ClockOut Finish) Through queries I now have several table for Clock In, Lunch and Clock Out but im struggling to put them all in one table

 

[DCrake]

Is there a matching key that ties the clock events togather as being for the same person on the same day? If there is then you could use vba to troll through the table and append the records to a new table that can be used for your calculations. Can you supply a sample table for a number of employees on a couple of dates?

 

[gemma-the-husky]

i would think this is the issue

iif(Num mod 2 =1, "In" ,"Out") AS InOut,
iif(InOut="In", Num, Num-1) AS Pair

this determines every clockin/out at runtime

you OUGHT to set these at time of data capture, then your query can be modified to work quicker. I would also use a boolean to determine in or out - it will resolve quicker than text.

This could also identify two things

a) clockouts with no outstanding clockin - ie errors
b) orphan clockins - ie currently clocked in staff, some of which may be errors

can your clockin machine filter any of these, or does it just record consecutive time stamps on a given card.

 

[mousemat]

For this particular customer there is a matching key yes.

Here is the sample data.
Function Numbers are
1 = Clock IN
2 = Clock OUT
3 = Lunch
4 = Travel
5 = Medical
6 = Smoking

 

[mousemat]

Gemma

Unfortunatly, the terminals just record consecutive time stamps.

 

[gemma-the-husky]

can you just confirm

employee clocks in with a 1, and out with a 2

if he clocks off for lunch, with a 3 - does he clock back in with another 1

ie is it always

13, 14, 15, 13, 12 etc

or something different.

==============
anyway - i think you need to not use this table as your final table - you need to consider the best normalised table format suitable for you to handle these results.

what table structure do you have for your clock cards at present? are you working directly form this table?


Instead, you may just need to process this table and allocate matching sequence numbers against pairs of clockin/outs - that would be very quick.

I susopect a good way of doing this would be store a record for each block of paid for activity so you store

timeOn Timeoff

for each matched pair, and an employee may have say 3 or 4 of these in a day. (rather than 6 or 8 distinct records) That may be the easiest way of handling the data, as then you can calcluate at-work time without reference to previous/next rows - in which case you need to massage the sample list into this format.


I know older time systems would have multiple blocks

ie

clockin clockoff clockin clockoff clockin clockoff

this is bad - its not normalised, so its hard to handle- and it limits you to a maximum number of work sessions

 

[mousemat]

Yes, He clocks in with a 1 and clocks out for lunch with a 3, clocks back in for the afternoon with a 1 and clocks out to go home with a 2.

Im not going to be using this as the final table, this table is how it comes from the clocks to the sql table.

 

[gemma-the-husky]

heres a version -I had to delete your data to get it zipped up.

recopy your sample data, and run the function in the module

This produces normalised data in the table NewTimes

I added a "check" mode that checks for errors, but doesnt write out the table
This identifed 180 errors - ie times where the next action was not correct eg a 1 followed by a 1 - or a non-1 followed by a non-1

as a result of this, my module skips these, and goes on to the next record, and as a result it can match ins and outs incorrectly. ie it goes on until it finds a clock record that fits, which may be the wrong one.

Anyway - have a look

it took about 30 secs to process your table


View attachment SampleTransactions_v2.zip

 

[DCrake]

Like Dave I has a play with your data, but I came accross some issues which Dave may have pointed out. Lets say you have one person who clocks in in the morning at 7am. Then goes for a smoke at 8am, 9,am, 10am, 11am. Then goes for lunch, comes back, travels to a meeting, comes back, goes out on call to another site, comes back, goes for another fag, clocks off and goes home.

This is a bit extreme but could still happen. You would have to sequence your daya by card id by transtime. You would then have to decide is this a repeat event or not. It would be impossible to have a flat record for each date per card id as you cannot predetermine how many times an activity going to occur.

 

[mousemat]

Dave

That works well, but as you and david have both mentioned, it is prone to error especially when people 'forget' to clock or as in the extreme case mentioned by David, smokes several times and then travels etc.

But it has given me more food for thought.

Thanks for you very welcome help