Aside from cleaning up your field names, this code should read almost exactly the way you need. I've standardized all your field names on underscores rather than (blegh) spaces, and capitalized only the first letter of each word in the field name. Personally, I'd shorten all those names further, kill all the underscores and switch to CamelCase, but it's your database.
Code:
SELECT
DS1.Date_Of_Report As CurrentRead,
DS2.Date_Of_Report As PriorRead,
DS1.Computer_Name,
DS1.Disk_ID,
DS1.Disk_Size_MB As CurrentSize,
DS2.Disk_Size_MB As PriorSize,
(DS1.Disk_Size_MB - DS2.Disk_Size_MB) As ChgInSize,
DS1.Percentage_Used AS CurrentPct,
DS2.Percentage_Used As PriorPct,
(DS1.Percentage_Used - DS2.Percentage_Used) As ChgInPct,
DS1.Disk_Space_Used As CurrentUsed,
DS2.Disk_Space_Used As PriorUsed,
(DS1.Disk_Space_Used - DS2.Disk_Space_Used) As ChgInUsed
FROM
DiskSpaceStats AS DS1
INNER JOIN
DiskSpaceStats AS DS2
ON DS2.Computer_Name = DS1.Computer_Name
AND DS2.Disk_ID = DS1.Disk_ID
WHERE DS2.Date_Of_Report =
(SELECT MAX(DS3.Date_Of_Report)
FROM DiskSpaceStats AS DS3
WHERE DS1.Computer_Name = DS3.Computer_Name
AND DS1.Disk_ID = DS3.Disk_ID
AND DS1.Date_Of_Report > DS3.Date_Of_Report)
ORDER BY
DS1.Date_Of_Report DESC,
DS1.Computer_Name,
DS1.Disk_ID;
Explanation of SQL:
I've aliased your table two different ways (As DS1, As DS2) in order to join it to itself on Computer_Name and Disk_ID. DS1 represents the current record on any given date, DS2 represents the most recent record that existed prior to that date for the same computer and disk.
The WHERE clause is the way we determine what the "most recent prior record" is. We do a subselect on a third alias (DS3) of the same table, to find the latest (MAX) Date_Of_Report in the table for that computer_Name and DISK_ID, that is less than the record on DS1. Whatever date that might be, is the date of the record that we use for DS2.
If you wanted to do a rolling one-week comparison, you could modify the subselect to something like this:
Code:
WHERE DS2.Date_Of_Report =
(SELECT MAX(DS3.Date_Of_Report)
FROM DiskSpaceStats AS DS3
WHERE DS1.Computer_Name = DS3.Computer_Name
AND DS1.Disk_ID = DS3.Disk_ID
AND DS1.Date_Of_Report >= DateAdd("w",1,DS3.Date_Of_Report));
You can adjust the DateAdd function's first and second parm to change the rolling lookback to any desired time period.
PS Aircode means I typed it in without testing it. This is not aircode. If you change your field names to match the ones in this SQL, then the code will run as is, and produce something like the following against your test data.
Windows 7 32bit 
Access 2010 64bit
Reply With Quote
