How do I correct erros from another program?

[Guest]

Hello.

I'm creating a querie from another program. It register the time of each
employee get in and get out of work. But the machine that read cards and
register have problems, well it's not it's fault it'as just the way that it's
programed.

The problem is that sometimes the program reads twice the card and looks
like an employee enter and out at the same time. it can happens, but mostly
is the program that does that.

So what I want is to eliminate those record even if some good
register/recodset must be lost.

So I have this querie that returns this data:

-----------------------------------------------------------------------------
EmployeeNumber Date Time Discription
0001 15-05-07 08:00 In
0001 15-05-07 13:00 Out
0001 15-05-07 13:00 In
0001 15-05-07 13:00 Out
0001 15-05-07 14:00 Int
0001 15-05-07 17:00 Out

How do I remove those two extra records/register at 13:00h?

Regards,
Marco

 

[John Spencer]

You can try the following query. BUT backup your data first.

DELETE *
FROM YourTable
WHERE EmployeeNumber & "/" & [Date] & [Time] IN
(SELECT EmployeeNumber & "/" & [Date] & [Time]
FROM YourTable
GROUP BY EmployeeNumber & "/" & [Date] & [Time]
HAVING Count > 1)

I would actually run this as a SELECT query first just to see if it was
returning the records I wanted to delete.
SELECT *
FROM YourTable
WHERE EmployeeNumber & "/" & [Date] & [Time] IN
(SELECT EmployeeNumber & "/" & [Date] & [Time]
FROM YourTable
GROUP BY EmployeeNumber & "/" & [Date] & [Time]
HAVING Count > 1)
ORDER BY Employee, [Date], [Time]
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

This one is easy to fix! Put the word DISTINCT right after SELECT in the SQL
statement like so:

SELECT DISTINCT ......

OR

With the query in design view, go to View on the menu and select Totals.

Both should eliminate duplicates.

 

[Guest]

Hello. Thanks for your time. Both

But I'm affraid that my problem is a little bit more complicated and maybe
impossible to solve here in Access or my knowledge is not that good as
supposed to be. (and I like Access a lot. ok?)

Is just this. DISTINCT OR GROUP BY can help but there are some situations
here that are dificult.

My querie result on this:

Employee Data_GE Hora_GE Descricao
00001 03-01-2007 08:40 Entrada Torniquete 2
00001 03-01-2007 13:44 Saída Torniquete 2
00001 03-01-2007 13:45 Entrada Torniquete 2
00001 03-01-2007 13:45 Saída Torniquete 2
00001 03-01-2007 14:02 Entrada Torniquete 2
00001 03-01-2007 18:19 Saída Torniquete 2


Data_GE = Date
Hora_GE= Time
Descricao = Description


00001 03-01-2007 13:45 Entrada Torniquete 2
00001 03-01-2007 13:45 Saída Torniquete 2

This two records appear because sometimes the employee card system do not
open the doors at the first time, but the software register all the times
that the employee pass the the card, so sometimes the employee need to pass
the card more then once.

If you have any ideia, please help me.

Regards,
Marco

 

[Guest]

Hello again.

I figured out, if all In/ENter must have an OUT, so if I sum all In and make
the diference between all out the result is the same right?

Marco