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

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


"Marco" wrote:

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

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








"Andy Hull" wrote:

> 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
> 
> 
> "Marco" wrote:
> 
> > 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
> > 
> > 

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







"Andy Hull" wrote:

> 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
> 
> 
> "Marco" wrote:
> 
> > 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
> > 
> > 

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

My explanation is best there.

Regards







"Andy Hull" wrote:

> 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
> 
> 
> "Marco" wrote:
> 
> > 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
> > 
> > 

On Sun, 27 May 2007 09:51:01 -0700, Marco <Marco@discussions.microsoft.com>
wrote:

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

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" wrote:

> On Sun, 27 May 2007 09:51:01 -0700, Marco <Marco@discussions.microsoft.com>
> wrote:
> 
> >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]
> 

On Sun, 27 May 2007 16:12:01 -0700, Marco <Marco@discussions.microsoft.com>
wrote:

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

On Mon, 28 May 2007 02:53:01 -0700, Marco <Marco@discussions.microsoft.com>
wrote:

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

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" wrote:

> On Mon, 28 May 2007 02:53:01 -0700, Marco <Marco@discussions.microsoft.com>
> wrote:
> 
> >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]
> 

On Mon, 28 May 2007 10:02:00 -0700, Marco <Marco@discussions.microsoft.com>
wrote:

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

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















"John W. Vinson" wrote:

> On Mon, 28 May 2007 10:02:00 -0700, Marco <Marco@discussions.microsoft.com>
> wrote:
> 
> >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]
>