Gap report

[Steve@trop]

The database I'm working on is used to record machine readings several times a day. Management wants a report to show when there are large gaps in the time between readings (to show if/when they were missed). I'm calling this a "Gap Report". Each record is a set of readings and includes the system time from when the readings were recorded. I'm thinking the query would need to look at all the readings in the order they were recorded and then calculate the time difference between each of them. From there I would need to look at each of these calcuations to see if any are above a certain threshold. Can I do this with a query or will it require a function?

 

[plog]

Can I do this with a query or will it require a function?

Yes.

You would run a query based on your table and include a calculated field which calls a function to get the gap since the last record. This function will use the DMax () (http://www.techonthenet.com/access/functions/numeric/max.php) to determine the last record's time and then a DateDiff() (http://www.techonthenet.com/access/functions/date/datediff.php) to calculate the gap between the two.

 

[Steve@trop]

Thanks Plog,

That's close. I need it to calculate ALL the gaps, not just the latest one. Sometimes a reading is missed, then subsequent readings are done without much of a gap between them. I want to be able to pick out any large gaps between readings over a period of time.

Each shift is supposed to do 2 readings, there are 3 shifts per day. Let's say they run the report on Friday. They want to be able to see that on Tuesday, there was a 6-hour gap between readings. This is a routine thing, sometimes they miss readings because they are busy with unexpected things. Management just wants to be able to track this and account for it.

 

[JHB]

... , sometimes they miss readings because they are busy with unexpected things. Management just wants to be able to track this and account for it.

How do you determine that a reading is missed?
Take a look at the thread http://www.access-programmers.co.uk/forums/showthread.php?t=249977 post #8, it is nearly the same you want, I think, (but for hours/minutes instead of days).

 

[plog]

I need it to calculate ALL the gaps,

Because its in a query, that's exactly how my method would work. For every record it will find the prior reading and determine the gap between the current record and the prior one.

 

[gemma-the-husky]

i would probably get the readings in a query sorted in the way you want, and have a vba sub to process the query and calculate the gaps.

maybe make use a "temporary" field in the readings record.