How to only start with the employee In. / remove the Out if it's f

[Guest]

Hello. I'm creating a querie that lists all employee activity. So the querie
runs onver a table that has all In and Out of the employees registries.
Sometimes in the the first day is Out, it happens because the employee
forgets to register is Out of job. So, is it possible to remove that register
in order to have the first register of the day has a IN? Even better was
changing the date of that Out register to the previous date.

This is ok to happen because the door don't open if the empoyee don't make
all register regular. So in the new day if a Out is no detected on the system
is like the employee never Out. So before he can In he must register Out.

Imagine that I have this:
---------------------------------------------------------------------------------
Employe No. Date Time Description
1010 15-05-2007 08:00 OUT
1010 15-05-2007 08:01 IN
1010 15-05-2007 18:00 Out

and I need this:
--------------------------------------------------------------------------
Employe No. Date Time Description
1010 15-05-2007 08:01 IN
1010 15-05-2007 18:00 Out



This error hapens when the software detects this:
------------------------------------------------------------------------
Employe No. Date Time Description
1010 14-05-2007 08:01 IN

This means that in 15th May the employee must register the Out to be able to
In.

Any idea? Please help.

Regards,
Marco

 

[Guest]

Hi Marco

My examples below use a table name of tblRegister with columns of
EmployeeNo, RegDate, RegTime and Description.

Obviously, change these to your own. It is not recommended to use table or
column names with spaces or to use reserved words like date or time.

Make a backup of your table BEFORE trying these!

If you want to delete the rows use something like,

delete * from tblRegister
where Description = "OUT"
and RegTime = dmin("RegTime", "tblRegister", "EmployeeNo = " & EmployeeNo &
" and RegDate = " & format(RegDate, "\#mm\/dd\/yyyy\#"))

If you want to update them...

update tblRegister
set RegDate = dateadd("d", -1, RegDate), RegTime = "23:59"
where Description = "OUT"
and RegTime = dmin("RegTime", "tblRegister", "EmployeeNo = " & EmployeeNo &
" and RegDate = " & format(RegDate, "\#mm\/dd\/yyyy\#"))

Note: I used a time of 23:59 as we don't know the real time they left and,
presumably, we want it to be after any other times registered. You can change
this and / or make the query calculate a time if you need to.

And the date gets set to the day before but you might need to do a bit more
here if yesterday wasn't the last day they were in.

Also, the above work for all dates in the table - you may want to restrict
it to today or the latest.

Hope this helps

Regards

Andy Hull

 

[Guest]

Hi. Very kind from you. Thanks.

I will try and then I let you know. If something goes wrong can I came here
with again?

By the way. the discription of the OUT can be diferent? Such as "AUTO OUT"

Once again thanks.

King regards,
Marco

 

[Guest]

Hi. In both example returns me the message "Unknown"

In delete I used:
delete * from 2_tbl_Actual_Historico_Almoco
where Descricao = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_Ge = " & format(Data_GE, "\#dd\/mm\/yyy\#"))


and in update I used:
update 2_tbl_Actual_Historico_Almoco
set Data_GE = dateadd("d", -1, Data_GE), Hora_GE = "23:59"
where Descricao1 = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_GE = " & format(Data_GE, "\#dd\/mm\/yyyy\#"))

I had to made some changes because our date starts with Days and then months.

Any idea?

Regards,
Marco

 

[Guest]

Hi. Check my last post "Delete record if a specific value is the first of the
day"

My explanation is best there.

Regards

 

[John W. Vinson]

Hi. In both example returns me the message "Unknown"

In delete I used:
delete * from 2_tbl_Actual_Historico_Almoco
where Descricao = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_Ge = " & format(Data_GE, "\#dd\/mm\/yyy\#"))


and in update I used:
update 2_tbl_Actual_Historico_Almoco
set Data_GE = dateadd("d", -1, Data_GE), Hora_GE = "23:59"
where Descricao1 = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_GE = " & format(Data_GE, "\#dd\/mm\/yyyy\#"))

I had to made some changes because our date starts with Days and then months.

A literal date in an Access query *ignores* the regional date/time settings.
You must use either the American mm/dd/yyyy format, or an unambiguous format
such as dd-MMM-yyyy or yyyy.mm.dd. The format you are using *will not work
correctly*.


John W. Vinson [MVP]

 

[Guest]

Hello John.

why do you think the SQL code is not working on may table? Is always
returnin the error of unknown.

Regards,
Marco

 

[John W. Vinson]

Hello John.

why do you think the SQL code is not working on may table? Is always
returnin the error of unknown.

What is the actual error message and message number?

John W. Vinson [MVP]

 

[Guest]

Hello. the error message only says "Unknown" nothing else.

Regards,
Marco

 

[John W. Vinson]

Hello. the error message only says "Unknown" nothing else.

That does not appear to be an Access error message, then. Could it be coming
from your application???

Can you create a Query (in the query window) duplicating what your code is
trying to create? Does *it* give a message Unknown (or some other error
message)?

Remember... we cannot see your database. I have no idea how your tables are
structured or whether the query you're building will even work.

John W. Vinson [MVP]

 

[Guest]

Hello John.

I'm sorry all this trouble. But i'm really stucked and I have no one else to
make this kind of questions.

In portugal Access is not very used. but I like it.

So, let me give the structure of my table from where I wnat to delete or
update

Name of the table:
2_tbl_Actual_Historico_Almoco

Field Name Type
EmployeeNumber Text
MicroDate Number ( is the date im decimal)
Data_GE Text (this is the date, I know that
is as text)
Descricao Text
Calculos_Hora Number
Hora_GE Text (this is the time)

I'm trying to run this querie that Andy provide me:

delete * from 2_tbl_Actual_Historico_Almoco
where Description = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_GE = " & format(Data_GE, "\#mm\/dd\/yyyy\#"))

I alreday try to change the Hora_GE (time) and Data_Ge (date) to Date/Time
type of data.

What do you think?

<b>Thanks</b>

Regards once again,
Marco

 

[John W. Vinson]

So, let me give the structure of my table from where I wnat to delete or
update

Name of the table:
2_tbl_Actual_Historico_Almoco

Field Name Type
EmployeeNumber Text
MicroDate Number ( is the date im decimal)
Data_GE Text (this is the date, I know that
is as text)
Descricao Text
Calculos_Hora Number
Hora_GE Text (this is the time)

I'm trying to run this querie that Andy provide me:

delete * from 2_tbl_Actual_Historico_Almoco
where Description = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_GE = " & format(Data_GE, "\#mm\/dd\/yyyy\#"))

I alreday try to change the Hora_GE (time) and Data_Ge (date) to Date/Time
type of data.

If you will be sorting or searching by date, you would really be much better
off having Hora_GE and Data_GE stored together in a single date/time field.
You say they are Text fields now - in what format are they stored? Could you
post a couple of examples? It should be possible to create a new Date/Time
field and update it with the existing data, using the DateSerial and
TimeSerial functions.

Your SQL uses a fieldname "Description" but in your table this appears to be
"Descricao". Also you have an odd mix of quotes. Is this query being created
in VBA code, or is this the SQL view from the Query window?

Guessing at a few things - such as guessing that Data_GE is a Text field in
dd/mm/yyyy format - try copying and pasting this into the SQL window of a new
query. BACK UP YOUR DATABASE FIRST - delete queries cannot be reversed!

DELETE * FROM [2_tbl_Actual_Historico_Almoco]
WHERE [Descricao] = "Saída Torniquete 2"
AND [Hora_GE] = & "'" DMin("[Hora_GE]", "2_tbl_Actual_Historico_Almoco",
"[EmployeeNumber] = '" & [EmployeeNumber] & "' AND [Data_GE] = '" & [Data_GE]
& "'") & "'";

This will delete the earliest record on *every* day in the table for every
employee. It's not completely clear that is what you want to do!

John W. Vinson [MVP]

 

[Guest]

Hi. This looks a "cruzade".

Yes, if "Saída Torniquete 2" is the first record of the day I want to
delete. Why? Because if it stays on the table says that the employee Out
(Out=Saída) before he could In, and that cannot be possible. These situation
occours because sometimes employees "forget" to pass the card "saying" that
they are leaving the office. And because for every In on job, must have an
out, in the folling day the employee must past a card to out, the out of
previous day in to be allowed to pass the card to get in.

I tried your querie but returns me a erro. Sintaxe Error (operator missing).
This is not easy. And Yes, I chage the type of data do date and time and I
made the update.

I'm now triyng from another way. I create a querie that shows me those wrong
records. then I create another querie that shows me the unmatch records. I
think that for there I may go there. well.........

Best Regards,
Marco

So, let me give the structure of my table from where I wnat to delete or
update

Name of the table:
2_tbl_Actual_Historico_Almoco

Field Name Type
EmployeeNumber Text
MicroDate Number ( is the date im decimal)
Data_GE Text (this is the date, I know that
is as text)
Descricao Text
Calculos_Hora Number
Hora_GE Text (this is the time)

I'm trying to run this querie that Andy provide me:

delete * from 2_tbl_Actual_Historico_Almoco
where Description = "Saída Torniquete 2"
and Hora_GE = dmin("Hora_GE", "2_tbl_Actual_Historico_Almoco",
"EmployeeNumber = " & EmployeeNumber &
" and Data_GE = " & format(Data_GE, "\#mm\/dd\/yyyy\#"))

I alreday try to change the Hora_GE (time) and Data_Ge (date) to Date/Time
type of data.

If you will be sorting or searching by date, you would really be much better
off having Hora_GE and Data_GE stored together in a single date/time field.
You say they are Text fields now - in what format are they stored? Could you
post a couple of examples? It should be possible to create a new Date/Time
field and update it with the existing data, using the DateSerial and
TimeSerial functions.

Your SQL uses a fieldname "Description" but in your table this appears to be
"Descricao". Also you have an odd mix of quotes. Is this query being created
in VBA code, or is this the SQL view from the Query window?

Guessing at a few things - such as guessing that Data_GE is a Text field in
dd/mm/yyyy format - try copying and pasting this into the SQL window of a new
query. BACK UP YOUR DATABASE FIRST - delete queries cannot be reversed!

DELETE * FROM [2_tbl_Actual_Historico_Almoco]
WHERE [Descricao] = "Saída Torniquete 2"
AND [Hora_GE] = & "'" DMin("[Hora_GE]", "2_tbl_Actual_Historico_Almoco",
"[EmployeeNumber] = '" & [EmployeeNumber] & "' AND [Data_GE] = '" & [Data_GE]
& "'") & "'";

This will delete the earliest record on *every* day in the table for every
employee. It's not completely clear that is what you want to do!

John W. Vinson [MVP]