What do you actually want? You can restrict the query over a date range,
which will return a single row:
PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT
AVG(Xdata) AS Average,
STDEV(Xdata) AS [Standard Deviation]
FROM YourTable
WHERE SECOND([SampleTime]) MOD 5 = 0
AND [SampleDate] BETWEEN
[Enter start date:] AND [Enter end date:];
Or you can group it by a time period, e.g. month, which will return one row
per month:
PARAMETERS
[Enter year:] SHORT,
[Enter month:] SHORT;
SELECT
YEAR([SampleDate]) AS [Sample Year],
MONTH([SampleDate] AS [Sample Month],
AVG(Xdata) AS Average,
STDEV(Xdata) AS [Standard Deviation]
FROM YourTable
WHERE SECOND([SampleTime]) MOD 5 = 0
AND YEAR([SampleDate]) = [Enter year:]
AND MONTH([SampleDate]) = [Enter month:]
GROUP BY YEAR([SampleDate]), MONTH([SampleDate]);
You could of course combine the two, restricting the query by a date range
and grouping within that range.
Both of the above queries use parameters which will prompt you to enter the
values when you run the queries. I've assumed the columns are of date/time
data type. If they are text data type then to return the year and month the
corresponding string expressions to YEAR([SampleDate]) and
MONTH([SampleDate]) would be LEFT([SampleDate],4] and MID(SampleDate],6,2)
and the parameters for these would declared as TEXT(4) and TEXT(2) rather
than as SHORT. In the first query the parameters would, if the column is
text, be declared as TEXT(10) and the values entered in exactly the same
format as the data in the table (be careful here that the actual values are
not in fact like 20061002 and what you are seeing is a formatted value).
Ken Sheridan
Stafford, England
tobesus said:
Thanks again Ken. Unfortunately with this new query I'm only getting back one
single row: the average and st dev of ALL the data apparently.
When I look at my table in design view, my SampleDate and SampleTime fields
have a data type listing of Date/Time, but both of the queries you suggested
produced the same result.
Any idea what the problem could be? Thanks again!
:
Mea culpa, I wasn't thinking straight. The query shouldn't return the
individual date/time values at all, just restrict the set over which the
average and standard deviation functions operate to those values, i.e.
SELECT
AVG(Xdata) AS Average,
STDEV(Xdata) AS [Standard Deviation]
FROM YourTable
WHERE MID([Time], 7, 2) MOD 5 = 0;
Its not the format of the values which is important, but the data type of
the columns in the table. Are you sure they are of date/time data type. If
they are then the query would I think still be restricted to the correct rows
using the expression I gave you, but that would be fortuitous because of the
default format in which the value is shown (its actually a 64 bit floating
point number under the skin). If they really are of date/time data type a
better way would be to use the Second function:
SELECT
AVG(Xdata) AS Average,
STDEV(Xdata) AS [Standard Deviation]
FROM YourTable
WHERE SECOND([Time]) MOD 5 = 0;
Ken Sheridan
Stafford, England
:
Thanks Ken. I changed my field names to SampleDate and SampleTime and ran the
query you recommended and it came very close to working.
The query parsed the data into five second intervals as intended, but didn't
actually average the data over five seconds. Instead it averaged the data
over one data point (every fifth second) and therefore produced no standard
deviation values because there was no deviation.
I noticed in my original table that my SampleDate and SampleTime fields were
in Date/Time format, not Text format as you were assuming, and I'm wondering
if that's the problem.
I tried changing them to Text format and tried to save; Access would work on
this for a few seconds and then report that "Access cannot change the data
type. There isn't enough disk space or memory."
I'm working with 1.5 million rows of data (x 3 fields columns) and am using
a 2-yr old Toshiba laptop with 1.23 GB of DDR SDRAM.
Any thoughts? Thanks again for your help!
:
I'm a assuming your Date and Time columns are of text data type rather than a
formatted date/time or number data type.
You can use the Mid function to extract the seconds value form your Time
column's values and then by means of the Mod operator restrict the query to
rows per n seconds, so to return rows at 5 second intervals a query would
look like this,
SELECT [Date], [Time],
AVG(Xdata) AS Average,
STDEV(Xdata) AS [Standard Deviation]
FROM YourTable
WHERE MID([Time], 7, 2) MOD 5 = 0
GROUP BY [Date], [Time];
I'd recommend that you avoid Date and Time as column names, however, as
these are the names of built in functions. Wrapping the name in square
brackets should avoid any confusion, but names such as SampleDate, SampleTime
would be better.
Incidentally you should be able to return a true single date/time data type
value from your two text expressions with:
CDate([Date]) + CDate(Left([Time],8))
This would enable you to do date/time arithmetic on the returned values if
necessary, using the normal date/time functions available in Access.
Ken Sheridan
Stafford, England
:
Hi, I'm trying to analyze one simple column of data collected at 1-second
intervals over several weeks. Raw data I'm importing into Access look like
this:
DATE, TIME, XDATA
2006-10-02,11:15:45.000,1717
2006-10-02,11:15:46.000,1715
2006-10-02,11:15:47.000,1713
2006-10-02,11:15:48.000,1714
2006-10-02,11:15:49.000,1717
I'm trying to create a query that would give me the average and standard
deviation of the data (XDATA) over various time intervals. Ie. I may want to
average over 5-second intervals, or I may want to average over 10-intervals.
Ideally my query result would have two data colums (AVERAGE and STDEV), and
would also have two additional columns for the date and time that the average
refers to.
Seems like this should be fairly easy to do but I'm still learning. Any help
would be greatly appreciated!
