By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,667 Members | 2,611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,667 IT Pros & Developers. It's quick & easy.

SQL/Query to pick every "x"th value

P: n/a
Hi guys,

I have a user who wants to have a report that shows only the 10th
value recorded by his equipment. (i.e. His equipment records
temperature every 30 seconds, but he only wants to view the data at
every 5 minutes.)

I proposed using the "mod" function in a WHERE statement on the
seconds "datepart" to test if each datapoint is an interval of 5
minutes from the starting number.

Our concern is that there will be drift or reset in the datalogging
time stamps, and the MOD method will break because the seconds won't
be exact.

So, I know you can step through a recordset at an interval. And I
know you can do interval grouping on reports. But, I don't know how
to write the SQL to simply kick that out. Every search I do for "SQL
skip" or "SQL interval" gives me lots of basic questions about
skipping single records or finding dates "since" an event.

Is there an accepted way to set a query/SQL statement that simply
picks every 5th value? (Could I do a recordset stepper with a 5 on my
gotorecord interval and write each value to a temporary table? I
think so, but -yeck- that seems like a hack.)

TIA,

Jon

Apr 23 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
jonceramic wrote:
Hi guys,

I have a user who wants to have a report that shows only the 10th
value recorded by his equipment. (i.e. His equipment records
temperature every 30 seconds, but he only wants to view the data at
every 5 minutes.)

I proposed using the "mod" function in a WHERE statement on the
seconds "datepart" to test if each datapoint is an interval of 5
minutes from the starting number.

Our concern is that there will be drift or reset in the datalogging
time stamps, and the MOD method will break because the seconds won't
be exact.

So, I know you can step through a recordset at an interval. And I
know you can do interval grouping on reports. But, I don't know how
to write the SQL to simply kick that out. Every search I do for "SQL
skip" or "SQL interval" gives me lots of basic questions about
skipping single records or finding dates "since" an event.

Is there an accepted way to set a query/SQL statement that simply
picks every 5th value? (Could I do a recordset stepper with a 5 on my
gotorecord interval and write each value to a temporary table? I
think so, but -yeck- that seems like a hack.)

TIA,

Jon
There is a VBA + SQL solution here:

http://www.mvps.org/access/queries/qry0006.htm

--
Smartin
Apr 23 '07 #2

P: n/a
On Apr 23, 5:20 pm, Smartin <smartin...@yahoo.comwrote:
jonceramic wrote:
Hi guys,
I have a user who wants to have a report that shows only the 10th
value recorded by his equipment. (i.e. His equipment records
temperature every 30 seconds, but he only wants to view the data at
every 5 minutes.)
I proposed using the "mod" function in a WHERE statement on the
seconds "datepart" to test if each datapoint is an interval of 5
minutes from the starting number.
Our concern is that there will be drift or reset in the datalogging
time stamps, and the MOD method will break because the seconds won't
be exact.
So, I know you can step through a recordset at an interval. And I
know you can do interval grouping on reports. But, I don't know how
to write the SQL to simply kick that out. Every search I do for "SQL
skip" or "SQL interval" gives me lots of basic questions about
skipping single records or finding dates "since" an event.
Is there an accepted way to set a query/SQL statement that simply
picks every 5th value? (Could I do a recordset stepper with a 5 on my
gotorecord interval and write each value to a temporary table? I
think so, but -yeck- that seems like a hack.)
TIA,
Jon

There is a VBA + SQL solution here:

http://www.mvps.org/access/queries/qry0006.htm

--
Smartin- Hide quoted text -

- Show quoted text -
Thanks Smartin. The solution is okay and confirmed I should use the
MOD method I eventually used. The problem was that it relies on
adding a sequential number to your data. Not always something you can
do. In our case, we were pulling dated process values from an ODBC
link, so we couldn't.

However, the values were time stamped, and when we reviewed the data,
it turned out that no date-time stamp was more than +/-5seconds off of
the exact minute.

1:00:00
1:00:59
1:02:04
1:03:02
etc.

So, MOD worked just fine with a few modification. It's in another
guy's database, so I don't have the code, but the solution I think was
to add a column to the query with the equation.

MOD_TIME: datepart("n",[time_stamp]+#00:20:00#) MOD 5

The +20 seconds wiped out any error due to the time not being exactly
on "00" seconds. (Note: we could have used 29 or 30 seconds if the
error was greater.)

Then, we put "0" as the the sort criteria under MOD_TIME.

In the interest of Usenet posterity, there was something in our code
that made the MOD_TIME above choke when it ran the SQL with the "0" in
the sort. I'm still not sure what. But, I'm pretty sure we solved it
by making it read:

MOD_TIME: cdate(datepart("n",[time_stamp]+#00:20:00#) MOD 5)

Note: I had thought I'd need to take [time_stamp] - [first_time] and
then calculate total minutes since start data. I was concerned how
I'd get the [first_time] (probably dlookup), but we realized this was
unnecessary as we were tweaking the MOD statement. Since we wanted 0,
5, 10, and since an hour was divisible by 5 already, each new hour
simply set the value back to "0", and we were back to the same cycle
of MOD's being 0, 1, 2, 3, 4, 0, 1, 2, 3, 4....

Jon
Apr 27 '07 #3

P: n/a
jonceramic wrote:
On Apr 23, 5:20 pm, Smartin <smartin...@yahoo.comwrote:
>jonceramic wrote:
>>Hi guys,
I have a user who wants to have a report that shows only the 10th
value recorded by his equipment. (i.e. His equipment records
temperature every 30 seconds, but he only wants to view the data at
every 5 minutes.)
I proposed using the "mod" function in a WHERE statement on the
seconds "datepart" to test if each datapoint is an interval of 5
minutes from the starting number.
Our concern is that there will be drift or reset in the datalogging
time stamps, and the MOD method will break because the seconds won't
be exact.
So, I know you can step through a recordset at an interval. And I
know you can do interval grouping on reports. But, I don't know how
to write the SQL to simply kick that out. Every search I do for "SQL
skip" or "SQL interval" gives me lots of basic questions about
skipping single records or finding dates "since" an event.
Is there an accepted way to set a query/SQL statement that simply
picks every 5th value? (Could I do a recordset stepper with a 5 on my
gotorecord interval and write each value to a temporary table? I
think so, but -yeck- that seems like a hack.)
TIA,
Jon
There is a VBA + SQL solution here:

http://www.mvps.org/access/queries/qry0006.htm

--
Smartin- Hide quoted text -

- Show quoted text -

Thanks Smartin. The solution is okay and confirmed I should use the
MOD method I eventually used. The problem was that it relies on
adding a sequential number to your data. Not always something you can
do. In our case, we were pulling dated process values from an ODBC
link, so we couldn't.
[snip]

Hmm, I shall have to try that over ODBC. The method does not, AFAICT,
add anything to the data, neither in the table(s), nor in the result
recordset. It simply increments a static variable (in Access) every time
a row is retrieved, and limits the result set to those rows that pass
the MOD test.

Glad you found something that worked.

--
Smartin
Apr 27 '07 #4

P: n/a
On Apr 27, 5:07 pm, Smartin <smartin...@yahoo.comwrote:
jonceramic wrote:
On Apr 23, 5:20 pm, Smartin <smartin...@yahoo.comwrote:
jonceramic wrote:
Hi guys,
I have a user who wants to have a report that shows only the 10th
value recorded by his equipment. (i.e. His equipment records
temperature every 30 seconds, but he only wants to view the data at
every 5 minutes.)
I proposed using the "mod" function in a WHERE statement on the
seconds "datepart" to test if each datapoint is an interval of 5
minutes from the starting number.
Our concern is that there will be drift or reset in the datalogging
time stamps, and the MOD method will break because the seconds won't
be exact.
So, I know you can step through a recordset at an interval. And I
know you can do interval grouping on reports. But, I don't know how
to write the SQL to simply kick that out. Every search I do for "SQL
skip" or "SQL interval" gives me lots of basic questions about
skipping single records or finding dates "since" an event.
Is there an accepted way to set a query/SQL statement that simply
picks every 5th value? (Could I do a recordset stepper with a 5 on my
gotorecord interval and write each value to a temporary table? I
think so, but -yeck- that seems like a hack.)
TIA,
Jon
There is a VBA + SQL solution here:
>http://www.mvps.org/access/queries/qry0006.htm
--
Smartin- Hide quoted text -
- Show quoted text -
Thanks Smartin. The solution is okay and confirmed I should use the
MOD method I eventually used. The problem was that it relies on
adding a sequential number to your data. Not always something you can
do. In our case, we were pulling dated process values from an ODBC
link, so we couldn't.

[snip]

Hmm, I shall have to try that over ODBC. The method does not, AFAICT,
add anything to the data, neither in the table(s), nor in the result
recordset. It simply increments a static variable (in Access) every time
a row is retrieved, and limits the result set to those rows that pass
the MOD test.

Glad you found something that worked.

--
Smartin

Ah, I see what you're saying about the query now. (I originally
interpreted the "i" code as being similar to an autonumber as people
added the data.)

Either way, we were able to deal with the date code, so we didn't need
to use that function to generate the values. But, we did use the
exact same MOD and 0 concept in your link.

Thanks again!

Jon

Apr 28 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.