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
6 1821
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
I thought there was more to the distinct statement, I will try this out. Any other Ideas?
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 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 - 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
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.
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
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,...
|
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...
|
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:
...
|
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...
|
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...
|
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...
|
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)
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |