Can I convert second data to Minute average and median

[drkundu]

Hello:



I have data measured in every second for three things (NOx, NO and NO2T). I want to reduce each of these three things to minute-average and minute-median data. How can I do it in Microsoft Access? Below is the example of a sub-sample of data.

DateTime	NOx	NO	NO2T
0:00:28	1.063	0.074	0.989
0:00:38	1.27	0.071	1.199
0:00:48	1.494	0.067	1.427
0:00:58	1.509	0.064	1.444
0:01:08	1.528	0.084	1.443
0:01:18	1.566	0.105	1.46
0:01:28	1.585	0.125	1.461
0:01:38	1.603	0.126	1.476
0:01:48	1.599	0.11	1.49
0:01:58	1.584	0.099	1.484
0:02:08	1.569	0.089	1.48

Thanks,

Kundu

[June7]

Looks like every 10 seconds.

You want to group records by minute and find the average/median for each data? Access has intrinsic function for Average of records but Median requires custom VBA.

Not seeing m/d/y or AM/PM parts in the DateTime value. So unless this table holds only 1 hour of data, m/d/y AM/PM are important for summarizing.

[drkundu]

Looks like every 10 seconds.

You want to group records by minute and find the average/median for each data? Access has intrinsic function for Average of records but Median requires custom VBA.

Not seeing m/d/y or AM/PM parts in the DateTime value. So unless this table holds only 1 hour of data, m/d/y AM/PM are important for summarizing.

OK. Please let me know, how to do average. Yes, there is m/d/y with the timestamp. The data actually looks like below.

08/01/2015 0:10:58	1.269
08/01/2015 0:11:08	1.275
08/01/2015 0:11:18	1.284
08/01/2015 0:11:28	1.286
08/01/2015 0:11:38	1.286
08/01/2015 0:11:48	1.297
08/01/2015 0:11:58	1.308
08/01/2015 0:12:08	1.32
08/01/2015 0:12:18	1.34
08/01/2015 0:12:28	1.349
08/01/2015 0:12:38	1.355
08/01/2015 0:12:48	1.319
08/01/2015 0:12:58	1.28
08/01/2015 0:13:08	1.24
08/01/2015 0:13:18	1.255

[June7]

Try something like:

SELECT Format([DateTime], "mmddyyyy hhmm") AS MoDyYrHrMn, Avg(NOx) AS AvgNOx, Avg(NO) AS AvgNO, Avg(NO2T) AS AvgNO2T FROM tablename GROUP BY Format([DateTime], "mmddyyyy hhmm");

[orange]

From wikipedia
In colloquial language, an average is the sum of a list of numbers divided by the number of numbers in the list. In mathematics and statistics, this would be called the arithmetic mean. However, the word average may also refer to the median, mode, or other central or typical value.

The median is also the number that is halfway into the set. To find the median, the data should first be arranged in order from least to greatest. To remember the definition of a median, just think of the median of a road, which is the middlemost part of the road.

Your original data points appear to be every 10 seconds. So you would have to deal with six consecutive records.
I don't know what your example in post 3 means.

[drkundu]

OK. Please let me know, how to do average. Yes, there is m/d/y with the timestamp. The data actually looks like below.

08/01/2015 0:10:58	1.269
08/01/2015 0:11:08	1.275
08/01/2015 0:11:18	1.284
08/01/2015 0:11:28	1.286
08/01/2015 0:11:38	1.286
08/01/2015 0:11:48	1.297
08/01/2015 0:11:58	1.308
08/01/2015 0:12:08	1.32
08/01/2015 0:12:18	1.34
08/01/2015 0:12:28	1.349
08/01/2015 0:12:38	1.355
08/01/2015 0:12:48	1.319
08/01/2015 0:12:58	1.28
08/01/2015 0:13:08	1.24
08/01/2015 0:13:18	1.255

It is working. Thank you. Could you please refer an introductory tutorial where I can learn to write codes as you are writing?

[June7]

The code is SQL - it is the language that Access queries use. Start with http://www.w3schools.com/SQl/default.asp

And for building expressions in queries https://support.office.com/en-us/art...B-8B6EC5AED41F

The query I posted could be built using Access query designer.

Learning VBA or macro coding is something else.

[drkundu]

Looks like every 10 seconds.

You want to group records by minute and find the average/median for each data? Access has intrinsic function for Average of records but Median requires custom VBA.

Not seeing m/d/y or AM/PM parts in the DateTime value. So unless this table holds only 1 hour of data, m/d/y AM/PM are important for summarizing.

Is it possible to do interpolation in Access? For example, I have following data

DateTime Mixing Ratio

10/17/2015 00:00:00 0.0015
10/17/2015 00:10:00 0.0019
.
.
.
.


The time interval of the above data are 10 min. I want to interpolate data every 10 sec. It means that there will be interpolation of 58 datapoints between the above time period. I have the continuous measurements throughout the day at 10 min interval.

Thank you for your continuous help.

[June7]

This would involve VBA to write records to a table.

[drkundu]

How Can I change the above code to convert 1-sec data to 10-min average data? Thanks.

[June7]

Did you try suggestion in post 4 for averaging by each minute?

Grouping records into 10 minute intervals will be complicated.