Need help in getting the logic to derive table.

[10e5x]

Hi all,
I am faced with a very troubled scenario. I am given a .mdb from access 2003. The data are so messy and i am tasked to derive the working hrs.


TACS table:
EventDate StaffName EventTime Message
21/11/12 John 23:01:00 OUT
21/11/12 Peter 23:10:00 IN
21/11/12 Peter 23:10:05 IN
22/11/12 John 07:19:00 IN
22/11/12 Peter 07:20:00 OUT
22/11/12 Joe 07:30:00 IN
22/11/12 Mike 08:00:00 IN
22/11/12 John 17:00:00 OUT
22/11/12 John 17:01:00 OUT
22/11/12 Joe 19:01:00 OUT
22/11/12 Mike 19:30:00 OUT
22/11/12 Mary 22:00:00 IN
23/11/12 John 07:01:00 IN
23/11/12 Mary 08:00:00 OUT
23/11/12 Peter 09:00:00 IN


What i want to derive.
FinalTable:
ID EntryDT ExitDT SName WrkHrs
1 Unknow 21/11/12 23:01:00 John Unknown
2 21/11/12 23:10:05 22/11/12 07:20:00 Peter 8hrs10mins
3 22/11/12 07:19:00 22/11/12 17:00:00 John 9hrs41mins
4 22/11/12 07:30:00 22/11/12 19:01:00 Joe 11hrs31min
5 22/11/12 08:00:00 22/11/12 19:30:00 Mike 11hrs30min
6 22/11/12 22:00:00 23/11/12 08:00:00 Mary 10hrs
7 23/11/12 07:01:00 Unknow John Unknown
8 23/11/12 09:00:00 Unknow Peter Unknown

Hope u all will uds what i am trying to do. Cuz with the initial table i will nv ever derive the working hours so i need a FinalTable to derive working hrs.
Addition information: If there are multiple clock in before out, i will use the latest clockIn as EntryDT, and vice versa, use the latest clockOut as ExitDT.

Thanks,
10e5x

 

[JohnLee]

Hi,

It's very difficult to help you because your data is ambiguous, you have 5 instances of John,1 instance for teh 21st clocking out 3 instances for the 22nd 1 clocking in and 2 clocking out and one instance on the 23rd clocking out.

So is this John the same person through out?

If this is the same person, John must have clocked in at some point on the 21st in order for him to clock out.

How can John clock out twice and within a minute of each clock out, this part suggests to me that there is more than one person with the name John.

John doesn't clock out on the 23rd.

It appears to me that there is missing data which could identify more clearly whether or not you are dealing with more than one John or not.

You have a similar senario with Peter and others in you sample data set.

In order to arrive at your desired result, the problem of knowing if you are dealing with the same person mulitply times or more than one person needs to be resolved.

If there is indeed more than one person with the same forename then there needs to be additional data that can clearly and concisely indentify them from each other [i.e. it needs to be something that is unique to each individual, staff number springs to mind], once you have that then you can start to work on getting your desired result.

Regards

John [not your John in your data. lol]

 

[10e5x]

Hi,

It's very difficult to help you because your data is ambiguous, you have 5 instances of John,1 instance for teh 21st clocking out 3 instances for the 22nd 1 clocking in and 2 clocking out and one instance on the 23rd clocking out.

So is this John the same person through out?

If this is the same person, John must have clocked in at some point on the 21st in order for him to clock out.

How can John clock out twice and within a minute of each clock out, this part suggests to me that there is more than one person with the name John.

John doesn't clock out on the 23rd.

It appears to me that there is missing data which could identify more clearly whether or not you are dealing with more than one John or not.

You have a similar senario with Peter and others in you sample data set.

In order to arrive at your desired result, the problem of knowing if you are dealing with the same person mulitply times or more than one person needs to be resolved.

If there is indeed more than one person with the same forename then there needs to be additional data that can clearly and concisely indentify them from each other [i.e. it needs to be something that is unique to each individual, staff number springs to mind], once you have that then you can start to work on getting your desired result.

Regards

John [not your John in your data. lol]

Hi John,
haha not the john. Firstly thanks for replying. Glad at least someone attempted. As for your qns, the john and other staff is the same person throughout. John may not have a clock out on 21st its becuz the data i working on is a sample data where data is extracted is for a datetime range. I am thinking to exclude entries with no clockIn for the time being.

As for your second qns, why is there multiple clock out or in, i shall explain how the data is collected. The data is collected for a door access system whereby employees scan their card to enter. therefore they may have scanned more than once(thats why data is so messy), hence i am thinking for each time in, i will take the latest scan for that period (1 in 1 out) and for out i will take the earliest time out.

However my problem is that i do not know how to input the timeout to the correct entry event


Regards,
10e5x

 

[10e5x]

Hi JohnLee,

I have got an idea, but need your help in generating the sql statements.
Firstly convert EventDate + EventTime into single datetime field(EventDT) with the format (DD/MM/YYYY HH:MM:SS).
From the raw data, we could split it into 2 tables: Entry and Exit with values gathered by using Select ... From TACS Where Message = "In" or "Out" respectively.
After which, do a order by StaffName, EventDT in both table.
Now the tougher part. Create a new column name ExitDT with datetime data type in Entry table. After which insert the values to the Entry table by selecting EventDT from the Exit table with conditions.
The conditions......STUCKED...arrrg

Sad,
10e5x

 

[JHB]

One of the ways you can do it is, first to select and insert all the staffs there have locked in, into a table.
After that use a recordset to find staff names that have locked in also have locked out.
If a staff member found, use the recordset to update table fields with the data.

I've made a sample for you in the attached database, open the form "FindTime" and look at the code in the form.
Hope it will point you in the right direction.

 

[recyan]

1) Check out if below gives some ideas (qryCart is the final query supposed to give the results)
Note : 99%, there is a chance of some bug.

qryDistinctDatesStaff

SELECT DISTINCT 
	tblEvents.EventDate, 
	tblEvents.StaffName
FROM 
	tblEvents;

qryIn
Have taken the Minimum as criteria, assuming, a staff after coming to work, might go out for a smoke or some other purpose & then might come in again.

SELECT 
	tblEvents.EventDate, 
	tblEvents.StaffName, 
	Min(tblEvents.EventTime) AS MinOfEventTime, 
	tblEvents.Message
FROM 
	qryDistinctDatesStaff 
	LEFT JOIN 
	tblEvents 
	ON 
	(
		qryDistinctDatesStaff.EventDate = tblEvents.EventDate
	) 
	AND 
	(
		qryDistinctDatesStaff.StaffName = tblEvents.StaffName
	)
GROUP BY 
	tblEvents.EventDate, 
	tblEvents.StaffName, 
	tblEvents.Message
HAVING 
	(((tblEvents.Message)="IN"));

qryOut

SELECT 
	qryDistinctDatesStaff.EventDate, 
	tblEvents.StaffName, 
	Max(tblEvents.EventTime) AS MaxOfEventTime, 
	tblEvents.Message
FROM 
	qryDistinctDatesStaff 
	LEFT JOIN 
	tblEvents 
	ON 
	(
		qryDistinctDatesStaff.EventDate = tblEvents.EventDate
	) 
	AND 
	(
		qryDistinctDatesStaff.StaffName = tblEvents.StaffName
	)
GROUP BY 
	qryDistinctDatesStaff.EventDate, 
	tblEvents.StaffName, 
	tblEvents.Message
HAVING 
	(((tblEvents.Message)="OUT"));

The Final query to run :

qryCart

SELECT 
	qryIn.EventDate, 
	qryIn.StaffName, 
	qryIn.MinOfEventTime, 
	qryIn.Message, 
	qryOut.EventDate, 
	qryOut.StaffName, 
	qryOut.MaxOfEventTime, 
	qryOut.Message, 
	IIf([qryIn.StaffName]<>[qryOut.StaffName],"NameNoMatch","NameMatch") AS NameMatch, DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]) AS ValidData, 
	[qryIn.EventDate]+[MinOfEventTime] AS TheInDateTime, 
	[qryOut.EventDate]+[MaxOfEventTime] AS TheOutDateTime, DateDiff("n",[TheInDateTime],[TheOutDateTime]) AS WorkingHoursInMinutes, 
	Format([WorkingHoursInMinutes]\60,"0") & ":" & Format([WorkingHoursInMinutes] Mod 60,"00") AS WorkingHoursMinutes
FROM 
	qryIn, qryOut
WHERE 
	(
		((IIf([qryIn.StaffName]<>[qryOut.StaffName],"NameNoMatch","NameMatch"))="NameMatch") 
		AND 
		(
			(DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]))=0 
			OR 
			(DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]))=1)
		)
ORDER BY 
	qryIn.EventDate;

2) Another option would be to have in the Query getting Out times, a DateField which depending on the Time Out, will be assigned a Date which is one day before the Out Date or the Same Date as Out Date.
This DateField along with the Staff Name could then be used in a join with the qryIn query.
____________________
Would prefer the 2nd option.

Thanks

 

[10e5x]

One of the ways you can do it is, first to select and insert all the staffs there have locked in, into a table.
After that use a recordset to find staff names that have locked in also have locked out.
If a staff member found, use the recordset to update table fields with the data.

I've made a sample for you in the attached database, open the form "FindTime" and look at the code in the form.
Hope it will point you in the right direction.

Hi i cant see any code after opening the FindTime form. Anyway greatly appreciated u tried to help.

 

[10e5x]

Hi reycan,
I would like to thank you for your effort. I think have to use the second option. Do you mind showing your option 2 solution. I have done something similar to the first option before, will have bugs like getting negative working hours.

And just another qns, i need the date and time field to be concat into a single datetime field(currently are two text field). My super told me it will only work out if i compare datetime to datetime....


@all, do u want a excel sample of my data? my data have 12,000 records, i will only be able to pull some data out, say like 20k?

 

[10e5x]

To all who have replied,

Thanks so much for your efforts and input. I am not allowed to upload the .mdb file(company policy), but for my own sake and for the ones who wanted to help, i extracted a sample of 20k data from the source and put it in Excel. I hope u all can open that and load it into access and look at the data i have been trying to work on. The data in excel is already order by PASS_M which is the staffName.

Thanks so much

Appreciated,
10e5x

 

[recyan]

I have done something similar to the first option before, will have bugs like getting negative working hours.

Try out the 1st solution. Make a copy of your table & name it as tblEvents. If the field names are same as what you have shown in your 1st post. Then simply copy the queries as I have given & save them under the same names. Then run the final query & see what happens.

Hi reycan,
I would like to thank you for your effort. I think have to use the second option. Do you mind showing your option 2 solution.

This will depend on you being sure that a particular OUT time, if it is before a particular time say 11:30:00 AM, is actually corresponding to the previous days IN time, else it corresponds to the same days IN time.

Hi reycan,
And just another qns, i need the date and time field to be concat into a single datetime field(currently are two text field).

In the last query that I have posted, for eg :

TheInDateTime: [qryIn.EventDate]+[MinOfEventTime]

i.e. [EventDate] + [EventTime]

just check out if that helps.

Thanks

 

[10e5x]

This will depend on you being sure that a particular OUT time, if it is before a particular time say 11:30:00 AM, is actually corresponding to the previous days IN time, else it corresponds to the same days IN time.

Hi recyan,
Regarding to this, for now, to be as accurate i hope that for a particular Out time to match, it must be between than the latest IN time and the next IN time if it exits. If the next IN time does not exists, it must be between than the latest IN time and the NOW time. However if this is too difficult, i guess what u suggest is relatively good, any OUT time before 11:30:00 belongs to ytd IN time. However is it possible to do a check first, meaning OUT time must be later than IN time.

For e.g. In the entry db, John clocked in on 01/11/2012 07:01:00, 02/11/2012 07:11:00 and 02/11/2012 23:01:00.

In the exit db, John clocked out on 01/11/2012 17:01:00, 02/11/2012 17:15:00 and 03/11/2012 06:00:00.

So comparing the 2nd exit, i will take the nearest In time to my 2nd Exit and this In time must be a datetime happened before my 2nd exit. Then i slot the value into the 02/11/2012 07:11:00 entry. (is this possible?)

I tried your suggested code it works!!! After running qryCart, it returns me ValidData with all 0 values and for WorkingHoursInMinutes and WorkingHoursMinutes fields, some values are negative(happened to me previously)

BTW after running each query i really think u are getting close to the solution. and you are really skillful. The logics are so wonderful.

Thank you so much, much appreciated. Though i do hope u can help me throughout, u are my biggest hope now.

Thanks,
10e5x

P.S. I seriously think your option 2 might solve the negative working hours problem.

 

[recyan]

Before changing the qrycart to below, remove the 0 or 1 criteria in query design mode & see if the results of Number of days is correct.


Try adding the below to qryCart in the SELECT clause

IIf([TheOutDateTime]<[TheInDateTime],"Not Valid","Valid") AS Valid_Data_1

qryCart

SELECT 
	qryIn.EventDate, 
	qryIn.StaffName, 
	qryIn.MinOfEventTime, 
	qryIn.Message, 
	qryOut.EventDate, 
	qryOut.StaffName, 
	qryOut.MaxOfEventTime, 
	qryOut.Message, 
	IIf([qryIn.StaffName]<>[qryOut.StaffName],"NameNoMatch","NameMatch") AS NameMatch,
	DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]) AS ValidData, 
	[qryIn.EventDate]+[MinOfEventTime] AS TheInDateTime, 
	[qryOut.EventDate]+[MaxOfEventTime] AS TheOutDateTime, 
	DateDiff("n",[TheInDateTime],[TheOutDateTime]) AS WorkingHoursInMinutes, 
	Format([WorkingHoursInMinutes]\60,"0") & ":" & Format([WorkingHoursInMinutes] Mod 60,"00") AS WorkingHoursMinutes, 
[B]IIf([TheOutDateTime]<[TheInDateTime],"Not Valid","Valid") AS Valid_Data_1[/B]
FROM 
	qryIn, qryOut
WHERE 
	(((IIf([qryIn.StaffName]<>[qryOut.StaffName],"NameNoMatch","NameMatch"))="NameMatch") 
	AND 
	(
		(DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]))=0 
		Or 
		(DateDiff("d",[qryIn.EventDate],[qryOut.EventDate]))=1)
	)
ORDER BY 
	qryIn.EventDate;

Pls check out thoroughly.

Regarding to this, for now, to be as accurate i hope that for a particular Out time to match, it must be between than the latest IN time and the next IN time if it exits. If the next IN time does not exists, it must be between than the latest IN time and the NOW time. However if this is too difficult, i guess what u suggest is relatively good, any OUT time before 11:30:00 belongs to ytd IN time. However is it possible to do a check first, meaning OUT time must be later than IN time.

For e.g. In the entry db, John clocked in on 01/11/2012 07:01:00, 02/11/2012 07:11:00 and 02/11/2012 23:01:00.

In the exit db, John clocked out on 01/11/2012 17:01:00, 02/11/2012 17:15:00 and 03/11/2012 06:00:00.

So comparing the 2nd exit, i will take the nearest In time to my 2nd Exit and this In time must be a datetime happened before my 2nd exit. Then i slot the value into the 02/11/2012 07:11:00 entry. (is this possible?)

Will try & take a look asap.


Thanks

 

[10e5x]

Before changing the qrycart to below, remove the 0 or 1 criteria in query design mode & see if the results of Number of days is correct.

Hi recyan,

I do not uds this part. Mind explain it again what should i do? i am really dumb Thanks for really looking into my problem. Do u want my .mdb file?

Thanks,
10e5x

 

[recyan]

I do not uds this part. Mind explain it again what should i do?

It's just to check if the Number Of Days being shown by the query is correct & we have not committed any mistake.

Go to Design Mode for the query qryCart & remove the 0 or 1 criteria, which is there under the ValidData field.
The query will now return the Number of Days between the IN Date & Out Date, without the 0 or 1 day filter. Just visually confirm, that it is returning proper results.
Once you are sure, replace the 0 or 1 back in the criteria & then add the last line that I have suggested to the SELECT clause & see what happens.

Edit :
Missed out your line on posting the .mdb fille. Do it. Will be easier for some one to help.

Thanks

 

[10e5x]

It's just to check if the Number Of Days being shown by the query is correct & we have not committed any mistake.

Go to Design Mode for the query qryCart & remove the 0 or 1 criteria, which is there under the ValidData field.
The query will now return the Number of Days between the IN Date & Out Date, without the 0 or 1 day filter. Just visually confirm, that it is returning proper results.
Once you are sure, replace the 0 or 1 back in the criteria & then add the last line that I have suggested to the SELECT clause & see what happens.

Edit :
Missed out your line on posting the .mdb fille. Do it. Will be easier for some one to help.

Thanks

Hi recyan,
After following your instructions i got back -85, 0, 31 and 85 as values for ValidData field.
E.g.
TheInDateTime = 30/04/2012 23:32:42
TheOutDateTime = 02/05/2012 22:44:24
returns me the value -85.

TheInDateTime = 02/05/2012 18:47:46
TheOutDateTime = 01/05/2012 19:38:01
returns me the value 31.

TheInDateTime = 01/05/2012 07:00:10
TheOutDateTime = 01/05/2012 15:31:07
returns me the value 0.

TheInDateTime = 02/05/2012 07:03:54
TheOutDateTime = 30/04/2012 23:00:09
returns me the value 85.

I guess this is the problem with datetime format?

Sorry maybe i am not clear at all, but i would like to inform you: 02/05/2012 in my data context is 2nd may 2012 with the format DD/MM/YYYY. i needed them to be in this format. As for time i need it i the format of HH:MM:SS.
Note: the EventDate field and EventTime field are of text data type in my access 2003.

What should i do next? If u need my .mdb, please pm me your email pls
I will wait for your next instruction before making further changes
Thanks alot and so sorry for all the trouble, but really need your help.

 

[recyan]

I guess this is the problem with datetime format?

would like to inform you: 02/05/2012 in my data context is 2nd may 2012 with the format DD/MM/YYYY. i needed them to be in this format. As for time i need it i the format of HH:MM:SS.
Note: the EventDate field and EventTime field are of text data type in my access 2003.
Before proceeding any further,
1) I think we need to get the Date in the Excel formatted to
05/02/2012 i.e. 2nd May 2012.
2) The EventDate & EventTime need to be of Date/Time Type in the access table.

If u need my .mdb, please pm me your email pls

Just attach your db to the post.
If you are not allowed to attach, you can upload to
https://www.box.com/
& post the link here.
It is free & can be used for our purpose.

I have currently some problems at my end to download the db.

Thanks

 

[10e5x]

Just attach your db to the post.
If you are not allowed to attach, you can upload to
https://www.box.com/
& post the link here.
It is free & can be used for our purpose.

I have currently some problems at my end to download the db.

Thanks

https://www.box.com/s/3k2ijs25g8hl7iyjcuem

here the link. Btw the data in my source table are in 05/02/2012 just want to inform u it is to be read as 5th of may 2012(afraid u might be confused). I still want to keep my data displayed as 05/02/2012.
Haha
Please let me know if u need my source database
Thanks.

 

[recyan]

Btw the data in my source table are in 05/02/2012 just want to inform u it is to be read as 5th of may 2012(afraid u might be confused).

Now I am confused. Is it to be read as 2nd of May 2012 or 5th of Feb 2012.
If your data in source table is in MM/DD/YYYY format, then there is no problem. Import it as it is. Just see that EvenDate & EventTime are Date/Time field.
We want the Dates in our Access Table to be in MM/DD/YYYY format.

I still want to keep my data displayed as 05/02/2012.

Do not worry about Display part. We can display data whichever way we want.

Thanks

 

[10e5x]

Now I am confused. Is it to be read as 2nd of May 2012 or 5th of Feb 2012.
If your data in source table is in MM/DD/YYYY format, then there is no problem. Import it as it is. Just see that EvenDate & EventTime are Date/Time field.
We want the Dates in our Access Table to be in MM/DD/YYYY format.


Do not worry about Display part. We can display data whichever way we want.

Thanks

Sorry confusing u. is it to be read as 2nd of may. data in my source table are in DD/MM/YYYY format. And both field in source table are text field. Are u able to dwnload my .mdb with the given link?

 

[recyan]

1) IMHO, Unless we have the dates & times in MM/DD/YYYY format, it will be difficult to proceed. It can be done in access as well as the source Excel before importing. Would prefer it to be done before importing, as I believe it will be easier.
2) The EventDate & EventTime fields should be in Date/Time Format.
3) If and only if the above 2 conditions are satisfied, then you can proceed with checking the final results of qryCart, whether they are correct or not & what else needs to be done.
4) Taking a deep look at the source data & understanding all functional implications is a must. Can't emphasize enough on this point.
5) I have some issues at my end, due to which I am not able to take a look at the db. However, I have managed to take a look at the Excel you had posted earlier. Let's hope, some one manages to take a look at the db.

Thanks