Page 4 of 4 FirstFirst 1234
Results 46 to 59 of 59
  1. #46
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Your interpretation is correct. You could also override anything less than 1 with 1, or (better) anything less than

    I assume it's basically throwing a divide exception - the calculated value is too big to fit into the result field.

    I have not seen any way to add comments to SQL in Access. There is one in mainframe DB2, Informix, and SQL Server.

    This thread says it doesn't exist. http://www.pcreview.co.uk/forums/do-...-t1181090.html It also has a solution by MVP Lebans to create such a comment facility.



    I assume that's associated with the fact that Access is trying to present the SQL visually to the query builder, and generally squishes all our lovely formatting and such out before storing the query. Comments would either have to be stripped out in that process, or would be carried along through any future changes to the joins, possibly confusing later developers with erroneous comments. That's the reasonable explanation; you could also just say "because it's Microsoft."

  2. #47
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Thank you. I have done as I did before and have tried to annotate what I understand in the second SQL statement. Have I understood this all correctly?

    Code:
     SELECT 
    \* Select the following fields from the specified table and perform any calculations given /*
    Q1.CurrentDate, 
    Q1.Computer_Name, 
    Q1.Disk_ID, 
    Q1.CurrentSize, 
    Q1.CurrentUsed, 
    (Q1.CurrentSize - Q1.CurrentUsed) AS CurrentLeft, 
    Q2.DailyDelta, 
    \* Divide CurrentLeft/DailyDelta and turn the result into an integer (no decimal places) */
    Int(CurrentLeft/Q2.DailyDelta) AS EstDaysLeft, 
    \* Add the number of days in EstDaysLeft field to the CurrentDate field */
    DateAdd("d",EstDaysLeft,Q1.CurrentDate) AS EstFullDate
     
    FROM
    \* Any reference to Q1 above links to the UsageDeltas Query /*
    UsageDeltas AS Q1 
    \* Join the same query records as a different alias Q2 and perform the calculations */
    INNER JOIN 
    (SELECT 
              Computer_Name,
              Disk_ID,
    \* Divide the ChgInUsed and ChgInDate fields and provide an Average figure */
              AVG(ChgInUsed/ChgInDate) AS DailyDelta,
    \* Calculate the max (latest) date in the CurrentDate field */
              MAX(CurrentDate) As MaxDate
           FROM UsageDeltas
    \* Group the output by these fields and prefix the new fields with Q2 */
           GROUP BY Computer_Name, Disk_ID      )  AS Q2 
    \* Provide the rows there these conditions are met, giving only the latest (up-to-date) information by using the MaxDate field */
    ON (Q1.CurrentDate = Q2.MaxDate) AND (Q1.Disk_ID = Q2.Disk_ID) AND (Q1.Computer_Name = Q2.Computer_Name)
     
    WHERE
    \* Only provide the data where DailyDelta is above 0 (i.e. not a negative figure) */
    DailyDelta>0;

  3. #48
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Your comments are basically correct. Here's a slightly more specific explanation for two of them.
    Code:
    INNER JOIN 
    \* for each computer and disk, use aggregate functions against the UsageDeltas Query to calculate the average increase in usage and the highest current date, and give the summary results fields back via a a different alias (Q2) */
    (SELECT
    Code:
     
    \* From Q1, only return the records with the highest Currentdate (as returned by Q2) for the same disk and computer */
    ON (Q1.CurrentDate = Q2.MaxDate) AND (Q1.Disk_ID = Q2.Disk_ID) AND (Q1.Computer_Name = Q2.Computer_Name)

  4. #49
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Thank You! I think this finally makes sense

  5. #50
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    I do have one further question (on Christmas Eve of all days, right?!)... I'm thinking of seperating the reports so it only displays records where the EstDaysLeft is less than 365 (i.e within a year) so I can focus on those systems needing immediate attention.

    However when I add in the below; I get a 'Enter Parameter Value' message come up.

    Code:
     WHERE (((Q2.DailyDelta)>0)) AND (((EstDaysLeft<365)));
    The SQL for EstDaysLeft is

    Code:
     Int(CurrentLeft/Q2.DailyDelta) AS EstDaysLeft
    any ideas?

  6. #51
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yeah, Access can get confused if you ask it one too many things in the same query.

    1) Just name the query, and select from the query where (EstDaysLeft < 365), or

    2) Mathematically, this should also get you what you want:
    Code:
    WHERE ( (Q1.CurrentSize - Q1.CurrentUsed) < (365 * Q2.DailyDelta))
    Coincidentally, assuming that CurrentSize is always greater than CurrentUsed, that single test will kill all occurrences with DailyDelta less than zero, and simultaneously kill all crazy high numbers for EstDaysLeft and EstFullDate.

  7. #52
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Thanks - when you say 'name the query' do you mean create another query which only gives the information from the current query?

    I'm just thinking about the WHERE clause so I know what it's practically doing

  8. #53
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    I'm a little confused by that WHERE clause; I can understand (Q1.CurrentSize - Q1.CurrentUsed), which is free space, but what does 365 * Q2.DailyDelta provide? I'm not sure what the sum is doing?

    I thought something like this would have been required? As it happens this doesn't work and comes up with an error (division by zero).

    Code:
     (((Q1.CurrentSize-Q1.CurrentUsed)/Q2.DailyDelta)<365);

  9. #54
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    I think I may now understand this....

    This is providing the CurrentFreeSpace

    Code:
     (CurrentSize-CurrentUsed)
    This is calculating the yearly delta, by times'ing the dailydelta by 365.

    Code:
     (365*DailyDelta)
    So the below is displaying all records where the yearlydelta is bigger than the current free size. I believe this works because if the current free size was bigger than the yearlydelta the EstDaysLeft would be over 365 days the calculation is essentially being performed backwards?

    Code:
     (currentsize-currentused) < (365*DailyDelta)

  10. #55
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yep. Mathematically, it's the same test, but it simultaneously takes care of two different issues - the negative deltas and the greater-than-a-year-to-fill, both at the same time in one test, with minimal calculations.

    Okay, I believe I'm permitted to use the word "elegant" here.

  11. #56
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    Perfect :-) elegance at it's finest!

    I assume the below could have been in any order? I.e. (365 * DailyDelta) or (DailyDelta * 365)? Is there a specific need for it to be one way rather than the other? More for my future knowledge than anything :-)

    Code:
     (365*DailyDeta)

  12. #57
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yes, you can switch the order if you please.

    Without parenthesis, and with differing types of operations, you'd have to check the precedence rules, or add parenthesis to clarify the order of application.

    Within parenthesis, for multiplication or addition, mathematical commutativity always applies.

  13. #58
    dr4ke1990 is offline Advanced Beginner
    Windows 7 32bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    52
    thank you

    I really appreciate all your help with this; I'm sure it's been painful at times :P

  14. #59
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    No pain.

    Analysis and rework are merely tolls we pay to eventually reach excellence and elegance.

Page 4 of 4 FirstFirst 1234
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