Query by a time-stamp window

[Specialstuff]

Hi Guys,

Wondering if you can help me. I am new to the forum and like to use access as a drag and drop query man with some muddling through with SQL union queries and suchlike. Keen to take it further though.

So, I have some fault code data to analyse with date and time stamp. I will be querying this data against a lookup table (second table below) to check for groups of fault messages. The tricky bit for me is looking at these groupings that occur within five seconds of each other.......This data could be upto 1 million lines, obviously, I could separate date and time into two columns.

All help/thoughts appreciated.

Serial Time Fault
9522 05/05/2013 08:40:02 Fault-A
9522 05/05/2013 08:40:03 Fault-A
9522 05/05/2013 08:40:04 Fault-B
9522 05/05/2013 08:40:07 Fault-D
9522 05/05/2013 08:40:07 Fault-C
9522 05/05/2013 08:40:07 Fault-E
9522 05/05/2013 08:40:07 Fault-F
9577 05/05/2013 08:55:09 Fault -X
9577 05/05/2013 08:55:09 Fault -Y
9577 05/05/2013 08:55:09 Fault -Z
9522 05/05/2013 08:55:09 Fault -X
9522 09/05/2013 08:55:09 Fault -Y
9522 10/05/2013 08:55:09 Fault -Z
9522 11/05/2013 08:55:09 Fault -X
9522 12/05/2013 08:55:09 Fault -Y
9522 13/05/2013 08:55:09 Fault -Z
9522 14/05/2013 08:55:09 Fault -X
9522 15/05/2013 08:55:09 Fault -Y
9522 16/05/2013 08:55:09 Fault -Z
9522 17/05/2013 08:55:09 Fault -X
9577 18/05/2013 08:55:09 Fault -Y
9578 19/05/2013 08:55:09 Fault -Z
9579 20/05/2013 08:55:09 Fault -X
9580 21/05/2013 08:55:09 Fault -Y
9581 22/05/2013 08:55:09 Fault -Z
9582 23/05/2013 08:55:09 Fault -X
9583 24/05/2013 08:55:09 Fault -Y
9584 25/05/2013 08:55:09 Fault -Z
9585 26/05/2013 08:55:09 Fault -X
9586 27/05/2013 08:55:09 Fault -Y
9587 28/05/2013 08:55:09 Fault -X
9588 29/05/2013 08:55:09 Fault -Y
9589 30/05/2013 08:55:09 Fault -Z
9590 31/05/2013 08:55:09 Fault -X
9591 01/06/2013 08:55:09 Fault -Y

Combin Fault
000-001 Fault-A
000-001 Fault-B
000-001 Fault-C
000-001 Fault-D
000-001 Fault-E
000-001 Fault-F
000-002 Fault-X
000-002 Fault-Y
000-002 Fault-Z
000-003 Fault-P
000-003 Fault-Q
000-003 Fault-R
000-003 Fault-S
000-004 Fault-A
000-005 Fault-B

 

[jdraw]

In plain English, What exactly is the question you are asking of the data?

 

[Specialstuff]

If fault occurs, is it on it's own?(no other faults within 5 seconds). If so, lookup table 2 for combin number. (Some combins will just be one fault, but another element is that these faults will also be part of other combins that I am checking next))

If fault occurs with others (within 5 seconds), lookup table 2 to see if the group are a combin that is in the table (eg faults a to f occur within five seconds, therefore combin 000-001. If group is not in table b Write the group somewhere and then allocate a number for further analysis.

Hope that makes sense!.....

 

[Galaxiom]

While the query designer only supports equals joins, in SQL view you can have any kind of join.

So join the table to an alias of itself on the condition of being within five seconds. This should present records that are potentially associated with the same fault.

 

[Specialstuff]

Cheers, sounds like a great starting point. I can probably fumble my way through that. Any example of code welcome, how do we query "within 5 seconds"?

 

[Specialstuff]

So, I have it checking the alias for the same time but struggling with the time window part. Probably a more than less than? But seem to be making some typos as can't get it to work.......

I have:

SELECT Fault_List.[Time],
Fault_List.[Serial]
FROM Fault_List INNER JOIN Fault_List AS A
ON Fault_List.[Time]=A.[Time]
WHERE Fault_List.[Serial]<>A.[Serial]
ORDER BY Fault_List.[Time]

 

[Galaxiom]

ON A.[Time] BETWEEN A.Time AND DateAdd("s", 5, Fault_List.[Time])

This only looks forward so you don't get listings of the same pairs of records in both time directions.

 

[Specialstuff]

Thanks for that. I have inserted as below but get an error message saying....

Between operator without And in query expression 'A.[TIME] BETWEEN A.Tim'

SELECT Fault_List.Serial, Fault_List.Time, Fault_List.Fault
FROM Fault_List INNER JOIN Fault_List AS A
ON A.[Time] BETWEEN A.Time AND DateAdd("s", 5, Fault_List.[Time])
WHERE (((Fault_List.Serial)<>[A].[Serial]))
ORDER BY Fault_List.Time;

 

[Galaxiom]

Sorry. I posted a mistake.
ON A.[Time] BETWEEN Fault_List.[Time] AND DateAdd("s", 5, Fault_List.[Time]

If that doesn't work, try:
ON A.[Time] >= Fault_List.[Time] AND A.[Time] <= DateAdd("s", 5, Fault_List.[Time])

 

[Specialstuff]

Cheers. I thought that might be a typo and tried the first change you suggest. Access didn't like it. The second one gave me the same results as "="

So, I tried opening up the dateadd figure in case the formatting of my table was wacky but checked it is standard time. Anyway, when I try 1000 it seams to work but repeats each individual instance 3 times....most odd.

SELECT Fault_List.Serial, Fault_List.Time, Fault_List.Fault
FROM Fault_List INNER JOIN Fault_List AS A 
ON A.[Time] >= Fault_List.[Time] AND A.[Time] <= DateAdd('s', 1000,Fault_List.[Time])
WHERE (((Fault_List.Serial)<>[A].[Serial]))
ORDER BY Fault_List.Serial, Fault_List.Time;

 

[Galaxiom]

You should get multiple instances of the records combined with information from those records which are within the time window.