Access 2013 calculate a running average in query design problems

[Pusscat]

Hi,
I have been battling with this little problem of mine, searching the internet and not quite getting the output I need and I’m looking for a little help and guidance please! I have a little experience with access but I’m getting nowhere fast…


I have a table (see below) in Access 2013.

URN	Sample date	B1	S1	S2	Log10B1
73115166	10-May-12	973	500	185	2.9881
73115166	17-May-12	10	500	185	1.0000
73115166	23-May-12	10	500	185	1.0000
73115166	27-May-12	45	500	185	1.6532
73115166	07-Jun-12	18	500	185	1.2553
73115166	11-Jun-12	155	500	185	2.1903
73115166	15-Jun-12	10	500	185	1.0000
73115166	20-Jun-12	10	500	185	1.0000

Column 'URN' is short text (yep, i know it looks like numbers but they are text as some of these in future will have letters in them..), 'Sample date' is Date / time and the rest are numbers.
The ‘Sample date’ entries are all unique and ‘URN’ are identical. The 'Log10B1' (numbers) may have replicates in them and the data is sorted from oldest to newest on 'Sample date'. The table with the data is called ‘Log Tables’.

I’m trying to make a query to produce an output of the table and calculate a column which gives a running average (Runaverage) of the values in 'Log10B1' column, rather like the one below with the calculated field in yellow.

URN	Sample date	B1	S1	S2	Log10B1	Runaverage
73115166	10-May-12	973	500	185	2.9881	2.9881
73115166	17-May-12	10	500	185	1.0000	1.994056
73115166	23-May-12	10	500	185	1.0000	1.6627
73115166	27-May-12	45	500	185	1.6532	1.6603
73115166	07-Jun-12	18	500	185	1.2553	1.5793
73115166	11-Jun-12	155	500	185	2.1903	1.6812
73115166	15-Jun-12	10	500	185	1.0000	1.5838
73115166	20-Jun-12	10	500	185	1.0000	1.5109

I’ve tried variations of expressions in the Query design based on Davg([Log10B1],[Log Tables]) but I only get the average of the Log10B1 over the complete dataset. I’ve seen some ref to a Var(Davg….expression) with concatenation strings resent relating to variables (the ‘Sample date’?) but I’m at a loss where to start or what protocols. Can anyone help please?!

Many thanks.

[ItsMe]

I am failing to see the pattern with your Runaverage. Also, I am unable to understand what you are trying to accomplish with Davg([Log10B1],[Log Tables]).

Is there another way you can communicate the formula for Runaverage? What is Log Tables?

[IrogSinta]

You need to use a Nested query to accomplish this. Open a query in SQL view and paste the following in there:

SELECT T1.*, (SELECT Avg(T2.Log10B1) 
FROM Table2 AS T2
WHERE T2.[Sample date]<=T1.[Sample date]) AS RunAvg
FROM Table2 AS T1
ORDER BY T1.[Sample date]

Ron

[Pusscat]

Hi Ron,
I pasted that in the SQL window but it didn't work.

The table that holds the data is called 'Log Tables' . The SQL code you gave refers to T1, T2 and Table2.....do i need to change any of these in the SQL to the name 'Log Tables' and if so which ones please?
Ta

[June7]

Change Table2 to [Log Tables].

[IrogSinta]

Sorry I forgot to rename my test table (Table2) before I posted the code. I usually use NameOfTable in its place. So just as June7 said, rename Table2 to [Log Tables]. You need to include the brackets since you have a space in your table name. On a side note, it's preferable not to use spaces in your naming convention. Using either Underscores (Log_Tables), or CamelCase (LogTables) would be a better option.

Ron

[Pusscat]

Many thanks June and Ron, works a treat!

Taken your advice and taken the spaces out of my table names as well. Love the internet for this sort of help and guidance, much appreciated!