Average Over Time (Using Date Field)

[Xing]

I'm trying to determine the Average space usage over time.
I'm not getting how I query and present the details...

Table Appears as such:
VolName | volFreeSpace | runDate
C | 25 | 1/6/2014 11:03:04 AM
C | 23 | 2/6/2014 11:00:00 AM
C | 21 | 3/6/2014 11:00:00 AM
D | 18 | 1/6/2014 11:18:00 AM
D | 10 | 2/6/2014 11:00:00 AM
D | 4 | 3/6/2014 11:00:00 AM
E | 7 | 1/6/2014 11:02:00 AM
E | 3 | 2/6/2014 11:00:00 AM
E | 1 | 3/6/2014 11:00:00 AM

So how do I Grab the date and present the details in Access though the example below doesn't add the guestimated growth rate.



volName | volFreeSpace | AVG Use Monthly | Space Longevity in Months
C | 21 | 2 | 10.5 months
D | 4 | 7 | .5
E | 1 | 3 | .3 months

Any help sincerely appreciated.

- Xing

[June7]

To pull the latest date record, try TOP N nested subquery, review http://allenbrowne.com/subquery-01.html#TopN

Don't understand how the AVG Use Monthly and Longevity results shown are obtained. What is the calc you used?

[Xing]

I guess I should have stated that I'd like to pull the most recent Free Space and then simply divide that by the average use monthly. This is a basic calculation for now though over time I should probably be able to determine a year over year growth rate of some kind of %.

[rpeare]

SELECT tblVolumes.VolName, Max([volfreespace])-Min([volfreespace]) AS SpaceChange, (Max([volfreespace])-Min([volfreespace]))/(Count([volname])-1) AS SpaceAvg
FROM tblVolumes
GROUP BY tblVolumes.VolName;

from here you can calculate remaining time based on the free space left as of the most recent record. Just be aware that 'cleaning up' the servers as in archiving data and moving it off the server in the middle of a span will wreak havoc with this calculation:

This is my table:

VolName	VolFreeSpace	RunDate
C	25	1/6/2014
C	23	2/6/2014
C	21	3/6/2014
D	18	1/6/2014
D	10	2/6/2014
D	4	3/6/2014
E	7	1/6/2014
E	3	2/6/2014
E	1	3/6/2014

Save this query as qryMaxDriveDate

SELECT Max(tblVolumes.RunDate) AS MaxRunDate, tblVolumes.VolName AS MaxVolName
FROM tblVolumes
GROUP BY tblVolumes.VolName;

Create this query:

SELECT tblVolumes.VolName, Max([volfreespace])-Min([volfreespace]) AS SpaceChange, (Max([volfreespace])-Min([volfreespace]))/(Count([volname])-1) AS SpaceAvg, Max(IIf([rundate]=[maxrundate],[volfreespace],0)) AS SpaceLeft, (Max(IIf([rundate]=[maxrundate],[volfreespace],0)))/((Max([volfreespace])-Min([volfreespace]))/(Count([volname])-1)) AS TimeLeft
FROM tblVolumes LEFT JOIN qryMaxDriveDate ON (tblVolumes.RunDate = qryMaxDriveDate.MaxRunDate) AND (tblVolumes.VolName = qryMaxDriveDate.MaxVolName)
GROUP BY tblVolumes.VolName;

you can break apart the formulas to show how I constructed them.