My Regester. Sounding board?

[Mick Peters]

Hi I am posting this in the lounge as I do not have a specific question I am just hoping to sound out a few ideas.

I am in the process of creating a register where people only use a barcode scanner to log in and out.

I have most of the code together and have it working to a point.

The problem I am pondering is that if the person only comes in and goes home again then all works well they are registered in and if a fire drill is called they are shown as on site. IF they log out I match the in with the out and the no longer show as on site.

The problem comes if the log in then go out for a while, logging out, and then return. As I am matching on the persons name and the location they work in both in's match the 1 out.

I have thought about date and time stamps but they would not match. I have thought about counting the in's against the outs but the register would run for a week not day to day as we work 24 /6.5.

I have also thought about having the user scan in1 , in2, in3 and then the corresponding out but that relies on them remembering to scan the correct numbered entry exit.

I am not looking for the complete answer just some ideas about how others would approach it.
Any ideas gratefully received.

 

[J.Ty.]

Dear Mick,

To be honest, it seems you are facing a problem, which cannot be solved on the level of software alone.

The first of the scenarios you are describing stems from the fact that your people do not remember/want to cooperate and register every in and out.
So no matter how you organize registering ins and outs, you will have inaccurate information in the system and cannot hope to get accurate answers from it.

The second problem, to the contrary, is solvable, assuming that your people do cooperate and register every in and out. In this case you need to register time and date stamps of every in and out.
Upon registering in or out of a person, you should then check for the most recent registration of the same person.

IN: if the most recent move was OUT, everything is OK, and the person is on the premise. If the most recent was IN, something is wrong and you do not know what. Either the person leaves now but registers it as IN, or did not regsiter his/her previous OUT.

OUT: if the most recent move was in, everything is OK, and the person is not on the premise. If the most recent was OUT, something is wrong and you do not know what. Either the person comes IN now, but registers it as OUT, or leaves now but did not regsiter his/her IN.

J.Ty.

 

[Mick Peters]

Hi J.Ty.

Thank you for you reply and some good points.
I think I can over come the users all being requested to scan in and out although as you say may have to put in some error handling as it may not be 100% due to people forgetting or tailgating.

I can get them to indicate whether they are coming or going, They should know right, and store these values in 2 separate columns then match an 'IN' in 1 column to an Out in another but this can all go askew when there are multiple in and outs during a day and some people come in on one day and go home on another. So I can't even do a match on date and name in for out.

Thanks again for your comments some food for thought.
Mick.

 

[J.Ty.]

Mick,

If your people do cooperate, then it is extremely simple.
You do not even have to store ins and outs (unless you need them for other purposes). You always keep only the last event (out or in) for each person, and nothing more. When somebody is registering, you compare it to the infomation about that person's last registration and you know what is happening.

J.Ty.

 

[Mick Peters]

Thank you J.Ty.
Yes unfortunately we do need it for records, trying to have a fire register for those on site and a record of comings and goings. But I just had a thought, 1 a year not bad, If I store all the user names on a sheet and then when the scan into a user form their name and if they are in or out. I match that against the list and mark the user accordingly I then stamp the time and date in another cell or column. I could then filter all those that are in and the last time they came in. That way you would be able to see who was on site and when they arrived. If they arrived outside the normal parameters (over 12 hours ago) you could presume they had left without scanning and they were actually out.
Cheers again good to get the old grey matter going in different directions.
Mick.

 

[Mick Peters]

Ok So I am almost there after discussing this here and with a few other people I am almost ready to start to build. Thank you to everyone who added input. Here is what I have come up with.

Store 2 lists on a single sheet.

The first list will be the booking in and out which will simply find the last row where something was entered and add all of the fields form the user form in that row plus 1.

The second list will be a list of all the people that are authorised on site.
Match the entered name on the user form to a name in that list then in the cell next to the match on that row add in the direction the person is traveling in as scanned on the user form, Over writing this each time.

So the user form contains Name, Where the person is working (Office or Yard) and Which way they are going in or out.

As they scan each field the scanner moves them to the next. Once they have scanned in User name and location and Direction the change event in the direction starts the VBA that finds first, what row the users name appears in list 2 and stores it a variable. Then it moves to list 1 and searches for the last row plus 1. Then transfers all the information to that row and lastly transfers the direction to the Column plus 1 at the row of the variable then clears all the entries on the user form.

Long winded I know but this way I can keep a record of all the user movements in and out. I can also use the first list to print a roll call of all those marked as in.

Any thoughts anyone on a simpler slicker way.

I am always open to ideas.