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
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