Help with Time & Attendance database query (1 Viewer)

W

Whisper Tech

Guest
Hi All
First off let me say I'm not too hot at using MS Access so help is a necessity for me to tackle this problem. I have a small cleaning company in Ireland approx 45 employees, who go to work at different times on different days at different locations. So I decided to try and implement a Time & Attendance system with the following desired results: to be Real time reporting, operate in remote locations and Automated.
An employee clocks in using a text message from a mobile phone on a remote site and a server in the office imports the relevant data from the text message into a table laid out as follows {that was the easy part}.

Access Xp Table as follows:
DateTime........Remote-Site.Employee..Status
1/2/03 12:30:00 Killarney. 001 Clock-in
1/2/03 12:30:05 Killarney. 002 Clock-in
1/2/03 14:30:00 Tralee.... 011 Clock-in
1/2/03 15:30:00 Killarney. 002 Clock-out
1/2/03 15:30:06 Killarney. 001 Clock-out
1/2/03 15:30:06 Tralee.... 011 Clock-out

{now the hard part} I need a query to take the data above and present it as follows so I can calculate the time difference:
Result Required:

Employee....Clock-in........Clock-out.......Difftime
001 1/2/03 12:30:00 1/2/03 15:30:06 3hr
002 1/2/03 12:30:05 1/2/03 15:30:00 3hr
011 1/2/03 14:30:00 1/2/03 15:30:06 1hr
I have tried the following:

1/ Split the table into two tables In & Out using a make query. The In table holds the data for all the clock in times and the out table holds all the clock out information. That’s fine but I cant bring them together without lots of errors.

2/ I thought of displaying two query's in a form, one query for all the in times for a specific employee with the datetime field in ascending order and the same with the out times also with date time filed in ascending order therefore first record in the in times query matches/corresponding to the first record in the out timed .

But all with lost of errors, so I was wondering could someone/anyone send me help in the form of advice, sample databases, web sites, or even just a comforting thought to ease my dilemma. I have searched the web and have not come up with a solution please help thanks.



E-mail 123John@eircom.net
 

namliam

The Mailman - AWF VIP
Local time
Today, 10:38
Joined
Aug 11, 2003
Messages
11,696
Attached i made a DB for your First idea

Your second idea i think is not going to work, as in a table view like your suggesting your 'only' seeing the active record and are unable to do much (directly) with non active (tho visible) records.



Another solution to your problem might be to addapt your Import process and table...
DateTimeIn DateTimeOut Remote-Site Employee Status

Then on import of a time in create a new record (no time out present) then on time out update the records to fill DateTimeOut

Regards

The Mailman

P.S. I attached the DB in A97 format, for the less fortunate ppl among us... (eg Mile-O)

P.P.S. One note on the side.... Dont use '-' in fieldnames (or any names) in an accessDB (same goes for spaces and all other "special characters" like )(*&&^%$#@!) It will lead you down a bad road...
 

Attachments

  • attendance97.zip
    6.9 KB · Views: 1,036
Last edited:
W

Whisper Tech

Guest
Heed more help........

Thank you for your response Mailman i tried out your solution/example of a subquery and put some real life data into it, where the clock-in and out's are not always in sequence and some lapse over midnight. As you can see from the attached file there are still errors. I am looking through the archives to gain more advice/knowledge of sub query's and DateTime problems to sort this out and will work on this.

If i don’t have any success i’ll go with your second idea and make a table with a DataIn and DateOut fields, populate all the Employee RemoteSite clock in time from the original table and then write some query to populate the missing Out Time’s. I may need a bit of help with that but thanks for the ideas and anymore you might have to steer me in the right direction.
 

Attachments

  • test1.zip
    8.7 KB · Views: 594

namliam

The Mailman - AWF VIP
Local time
Today, 10:38
Joined
Aug 11, 2003
Messages
11,696
Well i didnt account for that one... now did i??? Ok here goes...

Taking into account that if someone works over(mid)night that there actual exit time will be (much) earlier in the day than the time they start and it will be tomorrow you can add an OR clause:

OR (DateIn = dateout -1 and TimeIn > timeOut) ... Psuedo but SQL in DB (SubQry_In-OutImprove1)
But then if someone works at the some location later that day and leaves again the same day, it doubles up again... So one has to change the first where as well.. That is where i get stuck....

Problems occure when ppl leave the next day before they started the day before... Adding something like i did in SubQry_In-OutImprove2 (Number of hours <= 11) Eliminates that, but i dont know if its workable.

Hope it helps...

Regards

The "Flying Dutch" Mailman

PS. In is a reserved word DONT use that as a query name! Try to stick to calling it qry or que for query's then tbl for tables etc....
 

Attachments

  • clockinoutimproved97.zip
    10.8 KB · Views: 906

SSDB

Registered User.
Local time
Today, 02:38
Joined
Jul 25, 2015
Messages
11
thanks for sharing info

but i can not open MS Access 1997 ... because i am using 2013 v ... so please can any one convert it and re upload it ....

thanks and regards
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:38
Joined
Sep 21, 2011
Messages
14,050
thanks for sharing info

but i can not open MS Access 1997 ... because i am using 2013 v ... so please can any one convert it and re upload it ....

thanks and regards

I can only do up to 2007.

HTH
 

Attachments

  • ClockInOutImproved07.accdb
    396 KB · Views: 122

SSDB

Registered User.
Local time
Today, 02:38
Joined
Jul 25, 2015
Messages
11
thanks Gasman , it is helped ... but the result not always correct !

examples
if the employee clock-in and not clock-out ... will not appear
if the employee clock-out and not clock-in ... will not appear
if the employee clock-in and clock-out 2 times in the same day the result will duplicate 4 times

so how we can fix this my dear? ...


regards
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:38
Joined
Sep 21, 2011
Messages
14,050
Do not know to be honest. I have not looked at it and will not have a chance for a while.
As with any supplied code, you need to understand how it works. Only then can you tweak it to do as you want.

Really you should be ensuring the above first two scenarios are not happening.
For the last look at DSum perhaps?
 

Users who are viewing this thread

Top Bottom