Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 59
  1. #16
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Each one references a separate copy of the DiskSpaceStats table. Search for "DiskSpaceStats AS DS1", "DiskSpaceStats AS DS3", "DiskSpaceStats AS DS3"



    Remember, Access thinks of everything in terms of records. There's no intrinsic relationship between the record for Server1, Disk A on 4/4/13 and the record for Server1, Disk A on 4/5/13. If they're in the same table, the only thing Access can do with them is aggregate them - sum, average, max, min.

    So we have to fool Access into thinking of the table as if it were at least two different tables, and make very sure that Access understands what we are trying to do with each copy of that same data.

    Think of DS1 as the "real" table.

    Think of DS2 as a shadow copy of that table, from which we are going to somehow pick out the prior day's data. It's joined on Computer name and Disk ID, and then we have to join them on some comparison of the dates.

    Think of DS3 as a "stub" of the table, just to determine the date of DS1's right prior day.

    It's possible that there's some way to pull the DS2 data out without having a distinct DS3, but I'm not confident in, for example, trying to sort records inside a GROUP By clause, descending, on a field that doesn't appear in the GROUP BY in order that we could use the First() aggregate function to take all the data from the latest qualifying date. It might be possible, but that route feels "twitchy" to me.

    I know the method coded above will clearly work, because (A) I understand it thoroughly, and (B) I've tested it.

  2. #17
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Hi Dal,

    Firstly thank you for explaining; I really appreciate it. I don't doubt that any of this will work but I like to understand the code that I'm entering into my database; because it helps me learn and just in case I ever need to replicate it for another task etc..

    I'm now going to break the code down into sections as some of it I do understand, but I'm still not 100% sure on EVERYTHING! I would really appreciate any further explanation...

    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
    This I understand :-)

    Code:
     FROM 
       DiskSpaceStats AS DS1
       INNER JOIN 
       DiskSpaceStats AS DS2
       ON DS2.Computer_Name = DS1.Computer_Name 
       AND DS2.Disk_ID = DS1.Disk_ID
    I think I understand this also - it's a fairly straight forward INNER JOIN; but you're actually doing this with the data in one table rather than two. I dont think (!!) this is where the trickery comes in... I could be wrong through.

    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 > DS3.Date_Of_Report)
    So this is where things start to get confusing.... So we are saying that DS2.Date_Of_Report has to equal DS3.Date_Of_Report. DS3.Date_Of_Report equals everything that is prior to the date of DS1.Date_Of_Report and the MAX function is used just to pull out the latest date (which is still prior to DS1.Date_Of_Report) thus giving us the prior date?

    So....

    DS1 is defined as the normal table as you say, so there doesn't need to be any further defining.

    DS2 is defined by it equaling the same date as DS3 and the rest of the data (Disk_Size_MB, Percentage_Used, Disk_Space_Used) is pulled from the table by looking at the date defined in DS2.Date_Of_Report?

    DS3 is defined purely by being prior to the date of DS1.Date_Of_Report and then the MAX of that?


    Have I nearly got it...?? :-)

  3. #18
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Also - there is still the issue I had with June's code that although this gives me the data I am unable to find the average 'ChgInUsed' per server without creating a query for each.

    Essentially what I want to do now is work out the average per server and then divide that by the 'current' free space, giving me an estimated time until space runs out. With this model I'd have to create a query for each server and then join them all back again once I've worked that out?

  4. #19
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    You have it exactly.

    Once you have the above query, you can use the data it returns to determine your average or your trend. You shouldn't have to do it for each server, but it sometimes helps to start with a subset of the data before trying moderately complicated subselects like this one.

    If you can give an example of what result you are trying to find, then I can help you work out the SQL for it.

  5. #20
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Thanks Dal,

    Take the below example as an example; which is an extract from the Database (minus the Server/Disk names). What I want to be able to do is find out the Average 'ChgInUsed' without creating 2 more queries (one for Server 1 & one for Server 2).

    So in the below example it would have two Averages: -253.3803109 for Server 1 and -1419.29 for Server 2. I can't see a way of finding out the average by server/disk combination without creating a query for each and finding the average out that way.

    Once I have this information I want to use the Average figure divided by the free figure (which will be worked out by CurrentSize-CurrentUsed As CurrentFree) to then give an estimated time left until it runs out. Obviously in the above examples this will be invalid because they are minus figures, but there is about 80 combinations in the data so to pull out the couple which will have X amount of days left would be beneficial....

    Hope that makes sense!!


    CurrentRead PriorRead Computer_Name Disk_ID CurrentSize PriorSize ChgInSize CurrentPct PriorPct ChgInPct CurrentUsed PriorUsed ChgInUsed
    18/10/2013 15/10/2013 Server 1 A 68,433 68,433 0 38.06 38.808526315789 -0.748526315789 26,045.60 26,557.84 -512.239013684
    18/10/2013 15/10/2013 Server 2 A 855,313 855,313 0 89.234791666667 89.570736842105 -0.335945175438013 763,236.77 766,110.16 -2873.38275840005
    15/10/2013 14/10/2013 Server 1 A 68,433 68,433 0 38.808526315789 38.800520833333 8.00548245599941E-03 26,557.84 26,552.36 5.47839180899973
    15/10/2013 14/10/2013 Server 2 A 855,313 855,313 0 89.570736842105 89.566666666667 4.07017543800237E-03 766,110.16 766,075.34 34.8127396500204

  6. #21
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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.

  7. #22
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Hi Dal - thank you for this. I'm still reading and trying to work out what this all means hence the delay :-) I hope to come back soon!

  8. #23
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Hi Dal,

    I've read this about 10 times, and I have some questions... Sorry!

    1) The first peice of code when you make the Query a table. Why do we need to make it a table? Also, does this code come at the end of the Query SQL? Otherwise how does it know the calculations of Chginsize etc.

    2) How have you calculated ChngInDate?

    I'll wait for these to be clarified before I try to read the 3rd or 4th code :-)

  9. #24
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    1) I started out that post assuming the existence of a table with certain field names on it. Access usually doesn't care whether something is a table or a query, so if you create a query with the same name and field layout, the code should work just the same. Unless Access gets picky and decides it's too hard. Try it with the query, and if it works, great. If not, then edit the query, switch to design view, and press the make-table button to turn it into a make-table query.

    2) Dates are stored in the form of a number that represents "number of days since Date X", where Date X is some arbitrary date at the beginning of Microsoft's universe, I believe #12/31/1899#. So, given any two dates, you can subtract them, to find out the number of days between them.
    Code:
       DS1.Date_Of_Report As CurrentDate, 
       DS2.Date_Of_Report As PriorDate,
      (DS1.Date_Of_Report - DS2.Date_Of_Report) As ChgInDate,

  10. #25
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Ah ok, thank you. so ChgInDate is basically a DateDiff?

  11. #26
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yup. Number of days between two dates. Presumably, you are trying to predict when a server disk will be "full", so you need to divide the increase in usage by the number of days, to get the increase in usage per day. Then you divide the unused disk space by that number to get the days remaining until full. Obviously, you don't want to let it get anywhere near 100% full, if you want a functioning system, though.

  12. #27
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Hi Dal,

    I'm going to break this down again, if that's ok, just to make sure I understand everything.

    This is essentially the first query. Nothing special here :-)

    Code:
     tblUsageDeltas
       CurrentDate,
       PriorDate,
       ChangeInDate,
       Computer_Name, 
       Disk_ID, 
       CurrentSize, 
       PriorSize, 
       ChgInSize, 
       CurrentPct, 
       PriorPct, 
       ChgInPct, 
       CurrentUsed, 
       PriorUsed, 
       ChgInUsed
    Is this peice of SQL performing the maths (changeinused/changeindate) for every line (i.e. if a server/disk combination is repeated 12 times due to 12 different dates, this is performing this calculation 12 times) and then averageing the output into one number... giving the average of daily growth? It is then grouped by Computer_Name & Disk_ID to give only one figure (and avoid the aggregation error in Access)?

    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;
    Is this query different to the first one? Or in addition to?

    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);
    I wont ask any questions about the last SQL bit just yet - as that will confuse me even more.

  13. #28
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The TD1 query gives you the extra Megabytes that are being used up each day.

    Each record's ChgInUsed/ChangeInDate will be calculated, and then all of the results will be averaged for each Computer/Disk combination.
    For our purposes, it doesn't matter if the computer calculates them for every record, then sorts them and averages each group, or if it sorts them and groups them, then calculates them and averages them.

    It is NOT averaging the ChginUsed for each group and dividing by the average ChgInDate, which would be a meaningless number.

    The TD2 query finds the most recent record for each computer and disk. You have to use a subquery because you don't want only the records that match the latest record of any kind in the database, you want the records that match the latest record for each specific computer and disk.

  14. #29
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    So is the four SQL parts you've provided 4 queries, or do they fit into 1 query?

  15. #30
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    The fourth piece of code is a combination of the second and third.

    For demonstration purposes, the first piece of code is a table, but for your purposes, you would need to create a query that generates that layout as its output. I believe I made a demo query like that on the first page somewhere.

    In the fourth piece of code, you would then replace "tblUsageDeltas" with the name of the query you made to create the table structure.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-27-2013, 09:44 AM
  2. Replies: 3
    Last Post: 04-24-2013, 10:44 AM
  3. Replies: 5
    Last Post: 11-27-2012, 10:24 PM
  4. Compare tables and tell me differences
    By cowboy in forum Programming
    Replies: 2
    Last Post: 08-11-2010, 08:32 AM
  5. INNER JOIN differences from SQL Server
    By naurispunk in forum Queries
    Replies: 0
    Last Post: 07-22-2010, 03:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums