Show records 15 minutes after another record?

[Gareth Jones]

Hi all,

Is it possible to show records that happen a specific amount of time after a filtered record?

For example: If I was to filter a table of alarms by a keyword, could I show any records that happen 15 mins for example straight after?

This is show whether the subsequent alarms are related to the original filtered alarm.

Thanks in advance,

Gareth

[jimatqsi]

Gareth,
Records don't "happen." They are created and then maybe later they are modified. In the case of the "happen" being the creation, I mark every record of every data base, whenever possible, with a create date/time stamp. I do that by setting the default value of the creationdate to =Now() - if the table is an Access table. Other data storage tools have different functions for returning the current time.

Doing that then makes it simple to query the database based on creation time. Last modified time is only a little more challenging, but the same concept applies.

Jim

[zmbd]

Opps. re-read the question...

You can create a filter that pulls records based on the interval.

Busy with the kids for the moment, however, it shouldn't be too difficult provide you have a date and TIME field(s)

Can you provide us with the structure of your table and any SQL that you've already attempted?

[Gareth Jones]

So basically I have a raw table of alarms with fields such as time/date, the description of the alarm etc. I am hoping to be able to filter by a keyword such as electrical failure and then see the subsequent alarms that come in due to the electrical failure.

I haven't attempted any SQL yet as I'm still trying to get my head around how it would even work :)

I am thinking something along the lines of:
=IIf([alarm] Like "*electric*",Between [datetime] And DateAdd("m",5,[datetime]))

Would appreciate any ideas you have.

Thanks

[zmbd]

Yes, I understand what you want; however, you haven't provided enough detail as to the actual table structure forms etc...

There are several ways to do what you want and they all depend upon your table structure.

Please check your bytes.com inbox as I'm going to PM you a copy of tutorial, tools, and reference sites that will help you get a handle on things (^_^)

[Gareth Jones]

Thanks for the links, they are very helpful.

The structure of the table is very basic. There are no other linked tables, its just a table of raw data with many fields, however the only 2 fields I am concerned about is the time/date and the alarm itself.

I have included an example of the data. If it was to work, I would pick up the word 'electrical' and then only want to filter the next 5 minutes worth of alarms, which in this case would be rows 2 to 5 :)

ID Date/Time Alarm
1 01/01/2014 00:01:00 Electrical Failure
2 01/01/2014 00:02:00 Other Failure 1
3 01/01/2014 00:02:45 Other Failure 2
4 01/01/2014 00:03:00 Other Failure 3
5 01/01/2014 00:04:01 Other Failure 4
6 01/01/2014 00:07:00 Other Failure 5
7 01/01/2014 00:08:00 Other Failure 6

Thanks again for your help.

[jimatqsi]

Gareth, you can use the DateDiff function to select records based on a difference of time. See http://office.microsoft.com/en-us/ac...001228811.aspx

So once you have the time of the event you can compare that time to another time with

Datediff("n",date/time1,datetime2)

to get the difference in minutes.

And if the later date/time is first you will get a negative number.

Jim

[Gareth Jones]

Thanks Jimatqsi, however I'm not sure how I can incorporate the Datediff function in this case? There is only one time which is the start time.
Also, if you filter by the keyword 'electrical', I'm unsure how to show the subsequent 'unrelated' records for the next 5 minutes as the records in the query are filtered.
I'm not entirely sure what I need is even possible...

[Gareth Jones]

I believe I got it :)

I filtered the table by the word electrical. Then using that query along with the original raw table in another query (without any relationship) I used the criteria 'Between [datetime] And DateAdd("n",-15,[datetime])' on the date from the filtered query but showed the alarm from the raw table. I also included the date from the raw table. It appears to be working perfect. I will sanity check the data now to make sure its correct.

[Gareth Jones]

The above way works perfectly. I ended up using Between [datetime] And DateAdd("s",-900,[datetime]) as seconds were more accurate.

Thanks everyone for their help :)