So, I'll start by assuming that the Query code up in post 14 worked. I'm going to change the names of the current and prior read dates and add a ChangeInDate field, because I think you really want the average daily change rather than the average change between readings.
Let's pretend that the query is a table. You can use a maketable query to make that true, and it might save some SQL headaches with using a complex subquery as a source for another complex subquery.
Code:
tblUsageDeltas
CurrentDate,
PriorDate,
ChangeInDate,
Computer_Name,
Disk_ID,
CurrentSize,
PriorSize,
ChgInSize,
CurrentPct,
PriorPct,
ChgInPct,
CurrentUsed,
PriorUsed,
ChgInUsed
Now, if you want the current average change in usage for each disk, you do this:
Code:
SELECT
TD1.Computer_Name,
TD1.Disk_ID,
AVG(TD1.ChgInUsed/TD1.ChangeInDate) AS DailyDelta
from tblUsageDeltas AS TD1
GROUP BY TD1.Computer_Name, TD1.Disk_ID;
And you need the most current record, only, for your future projections:
Code:
SELECT
TD2.CurrentDate,
TD2.Computer_Name,
TD2.Disk_ID,
TD2.CurrentSize,
TD2.CurrentUsed
FROM
tblUsageDeltas AS TD2
WHERE TD2.CurrentDate =
(SELECT Max(TD3.CurrentDate)
From tblUsageDeltas AS TD2
WHERE TD2.Computer_Name = TD3.Computer_Name
AND TD2.Disk_ID = TD3.Disk_ID);
So we join the first to the second something like this:
Code:
SELECT
TD2.CurrentDate,
TD2.Computer_Name,
TD2.Disk_ID,
TD2.CurrentSize,
TD2.CurrentUsed,
TD1.DailyDelta,
((TD2.CurrentSize - TD2.CurrentUsed) / TD1.DailyDelta) AS EstDaysLeft,
DateAdd("d",((TD2.CurrentSize - TD2.CurrentUsed) / TD1.DailyDelta),TD2.CurrentDate) AS EstFullDate
FROM
tblUsageDeltas AS TD2
INNER JOIN
(SELECT
TD1.Computer_Name,
TD1.Disk_ID,
AVG(TD1.ChgInUsed/TD1.ChangeInDate) AS DailyDelta
FROM tblUsageDeltas AS TD1
GROUP BY TD1.Computer_Name, TD1.Disk_ID)
ON TD2.Computer_Name = TD3.Computer_Name
AND TD2.Disk_ID = TD3.Disk_ID
WHERE TD2.CurrentDate =
(SELECT Max(TD3.CurrentDate)
From tblUsageDeltas AS TD2
WHERE TD2.Computer_Name = TD3.Computer_Name
AND TD2.Disk_ID = TD3.Disk_ID);
warning - I've ignored the possibility of negatives and zeroes in this aircode, so you may have to add an FIX() or INT() or IFF() in there somewhere.