473,320 Members | 2,133 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Remove Duplicate events.

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
Mar 3 '08 #1
6 1821
MindBender77
234 100+

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)
Expand|Select|Wrap|Line Numbers
  1. Select Event, Distinct(EventTimeUTC), Value from YourTableName
  2.  
Bender
Mar 3 '08 #2
I thought there was more to the distinct statement, I will try this out. Any other Ideas?
Mar 4 '08 #3
I get a syntax error when I use the following code

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

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
Mar 4 '08 #4
MindBender77
234 100+
I get a syntax error when I use the following code

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

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
Expand|Select|Wrap|Line Numbers
  1. SELECT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, DISTINCT Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
  2. FROM  Clean_Up_Filtered;
  3.  
Bender
Mar 4 '08 #5
I still get an error with that but it changes to
syntax error, (missing operator) in query expression 'DISTINCT Clean_Up_Filtered.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.
Mar 5 '08 #6
Stewart Ross
2,545 Expert Mod 2GB
I think the problem is with the parenthesis. Try
Expand|Select|Wrap|Line Numbers
  1. SELECT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, DISTINCT Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
  2. FROM Clean_Up_Filtered;
  3.  
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Clean_Up_Filtered.Tag, Clean_Up_Filtered.ActiveTimeUTC, Clean_Up_Filtered.EventTimeUTC, Clean_Up_Filtered.[Value For Doubles]
  2. FROM Clean_Up_Filtered;
-Stewart
Mar 5 '08 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Patrick G. | last post by:
Greetings all: ASP VB, SQL Svr 2000 I am pulling data from 3 tables. table1 holds item details table2 holds publication types and the item id from table1 table3 holds category types and...
7
by: Voetleuce en fênsievry | last post by:
Hello everyone. I'm not a JavaScript author myself, but I'm looking for a method to remove duplicate words from a piece of text. This text would presumably be pasted into a text box. I have,...
0
by: John Seaden | last post by:
Hi, anyone have any idea why I get duplicate changed events firing from a filesystemwatcher watching a file on a remote machine but not on the local machine? Any suggestions as to how to...
3
by: Franco, Gustavo | last post by:
How can I remove all event handler from one event without do -=? I won't explain why because is too long, and that the only option left I have right now. I have one event declared for: ...
1
by: TaeHo Yoo | last post by:
I have a table that has more than 1 milion rows so practically it is impossible to remove all duplicate rows by hand. Could you help me to remove those duplicate rows at all? This table doesn't...
14
by: BarrySDCA | last post by:
I have a database being populated by hits to a program on a server. The problem is each client connection may require a few hits in a 1-2 second time frame. This is resulting in multiple database...
20
by: David Levine | last post by:
I ran into a problem this morning with event accessor methods that appears to be a bug in C# and I am wondering if this a known issue. public event SomeDelegateSignature fooEvent; public event...
6
by: Dave | last post by:
I really don't like the users getting an unhandled expception page, and I'm still to new with ASP.Net and C#. So please accept my appology for the 2 part question. SqlException (0x80131904) ...
4
by: FullBandwidth | last post by:
I have been perusing various blogs and MSDN pages discussing the use of event properties and the EventHandlerList class. I don't believe there's anything special about the EventHandlerList class in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.