Remove Duplicate events.

[Bishopkris40]

Please help.

I am working on a database that logs the alarm events from a machine. The problem I have is that I need to explain why the machine has stopped and calculate the time it stops for. I then report the total time stopped.

This is fine when only 1 alarm was present as the raw table only has 1 relevant entry at that time. But if there were two alarms then I get two entries at that time. I only need one of the entries at that time, It doesn't matter which one.

I have a query that runs and shows only the alarms so I can see the duplicates but I'd like to remove any duplicates based only on the time column.

The table/query.

Event, EventTimeUTC, Event Value.

I want to show all of these but only the first occurance by event time EventTime.

I have tried everything I can think of but I always return all rows.

Below is an example of where I need this to work. I only want to see one alarm here it doesn't matter which.

Event EventTimeUTC Value
Flags (00-15) 28/02/2008 20:38:41 524288
Flags (96-111) 28/02/2008 20:38:41 536870912

I cannot alter the original table and would prefer to have just a simple query showing the first occurence by time.

I hope someone can help, I have spent hours surfing the net for an answer.

Regards
Kris Bishop

[MindBender77]

I have tried everything I can think of but I always return all rows.

Below is an example of where I need this to work. I only want to see one alarm here it doesn't matter which.

Event EventTimeUTC Value
Flags (00-15) 28/02/2008 20:38:41 524288
Flags (96-111) 28/02/2008 20:38:41 536870912

You could write a query using "Distinct" which will only display data once. You can add this in the SQL view in the query designer. I believe it might help in your case.
Example (Something Like This)

Select Event, Distinct(EventTimeUTC), Value from YourTableName
 

Bender

[Bishopkris40]

I thought there was more to the distinct statement, I will try this out. Any other Ideas?

[Bishopkris40]

I get a syntax error when I use the following code

SELECT Clean_Up_Filter ed.Tag, Clean_Up_Filter ed.ActiveTimeUT C, DISTINCT (Clean_Up_Filte red.EventTimeUT C), Clean_Up_Filter ed.[Value For Doubles]
FROM Clean_Up_Filter ed

I can do something that will sort things out at the report time, but this has to be a running query so I can report in real time, I have other software for this but I need the raw data to update as a query.

I'm using Access 2002 SP3

[MindBender77]

I get a syntax error when I use the following code

SELECT Clean_Up_Filter ed.Tag, Clean_Up_Filter ed.ActiveTimeUT C, DISTINCT (Clean_Up_Filte red.EventTimeUT C), Clean_Up_Filter ed.[Value For Doubles]
FROM Clean_Up_Filter ed

I can do something that will sort things out at the report time, but this has to be a running query so I can report in real time, I have other software for this but I need the raw data to update as a query.

I'm using Access 2002 SP3

I think the problem is with the parenthesis. Try

 SELECT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, DISTINCT Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
 FROM  Clean_Up_Filtered;
 

Bender

[Bishopkris40]

I still get an error with that but it changes to
syntax error, (missing operator) in query expression 'DISTINCT Clean_Up_Filter ed.EventTimeUTC '

Thanks for your help so far,

I am working on creation of a new table that has an indexed key on event time which should eliminate the doubles at source, but this is meaning that I am logging twice as much data so that I have a log of double alarms.

This is why I prefer to do this in a query so I can have all the raw data and pull out only the relevant infomation.

Please help solve this issue.

[Stewart Ross]

I think the problem is with the parenthesis. Try

 SELECT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, DISTINCT Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
 FROM Clean_Up_Filtered;
 

Bender

Hi. The DISTINCT keyword immediately follows SELECT. Its effect is to return unique rows; it is not used as a field qualifier, hence your syntax problems. DISTINCT and the similar but non-standard DISTINCTROW are well-documented in the Access Help file.

SELECT DISTINCT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
FROM Clean_Up_Filtered;

-Stewart