Subract access Query

[dr4ke]

I have a database where, on a weekly basis, I will be entering around 20 lines. The columns are exported in from excel, with the date they were entered. A definition of the columns used are below...



1) SAN Name - The name of the SAN.
2) Aggregate Name - The name of the Aggregate, within the SAN.
3) Used - The amount of space used within the Aggregate
4) Total - The total amount of space in the Aggregate.
5) Date - The date the data was captured.

For example purposes, I currently have 3 sets of this data (03/01/2012, 09/01/2012, 16/01/2012). The only data that will change is the 'Used', 'Date' and possible the 'Total' columns.

What I would like is a query to run to show the 'different' between each SAN/Aggregate name (all the data will be held in the same table), so would expect to see an additional line every additional week there is data inputted; something like the below would be the ideal output.

SAN 1, Aggregate 1, Used -100, Total -0
SAN 1, Aggregate 2, Used +12, Total +50
SAN 2, Aggregate 1, Used -400, Total -0

Thanks,

[Rawb]

Do you really need the Total Field? That seems like something that's not going to change regularly. I'd also recommend changing the Used column to the total amount used and not the change in amount used over the week.

As for your Query, I'm not sure exactly what you're asking for. Are you wanting just the changes from the last week? Or are you wanting to see the total amount used since the beginning of data collection?

[dr4ke]

No not neccessarily, as you say it's not something that is going to change on a regular basis. Apologies, I don't think I was clear the 'Used' column is the 'Total Amount Used'.

What I'm looking for, from the query, is (as you say) the changes from the last week. Eventually what i'd like to capture is at what percentage it's growing every week, and then calculate how long it has until it runs out of space.

hope that makes a bit more sense?

[Rawb]

Ah HAH! That makes perfect sense now.

To make things easier on me, I'm going to assume that your Table name is Table1 and that what you've listed are the actual Field names within that Table. . .

What we need to find then are the following:

• The beginning date of the date range.
• The end date of the date range.
• The SAN name.
• The Aggregate name.
• The amount used at the beginning date.
• The amount used at the end date.
• The percentage of change of used data over the date range AS COMPARED TO THE TOTAL.
• The size of the date range (in days, to simplify - we can "do maths" to it later if we want to convert it into years/months)
• The number of "date ranges" until the amount used matches the total.

Obviously, this is going to be a complex Query (and will possibly require multiple Queries). So, before we get started on the actual Query, do you disagree with anything I've said above?

[dr4ke]

Excellent! :-)

Yes that sounds perfect! The only thing I will say, and I'm sure you've already thought of it, is that the aggregate names are duplicated across the SAN names. What I mean by that is "Aggregate 1" will be in "SAN 1", "SAN 2", "SAN 3" so ideally the query would have to search for both columns to find the duplicate

Hope I've not just confused things!!!

Thank you for your help so far!!

[Rawb]

Excellent! :-)

Yes that sounds perfect! The only thing I will say, and I'm sure you've already thought of it, is that the aggregate names are duplicated across the SAN names. What I mean by that is "Aggregate 1" will be in "SAN 1", "SAN 2", "SAN 3" so ideally the query would have to search for both columns to find the duplicate

Hope I've not just confused things!!!

Thank you for your help so far!!

Does that mean that SAN 1, Aggregate 1 is the same Aggregate as SAN 2, Aggregate 1? Or are those two different?

[dr4ke]

SAN 1, Aggregate 1 will be different to SAN 2, Aggregate 1.

[Rawb]

Whew, you had me worried there for a minute!

Arguably this would actually be simpler to just do it programattically in VBA. However, that would be more actual work because you'd also need a Temporary Table and some Forms to set everything up. So, a Query it is!

I did a little modeling and it looks like your specific needs will require two separate Queries. This is because the Queries themselves will be asking for user input (the date range, SAN name, and Aggregate name). And, apparently when you're using a GROUP BY clause (which will also be needed), you can't ask for user input in this manner.

So, Query One will ask for the user input and basically "filter" out all the Records that don't pertain to what we want. The Query Two will then do the work to reformat/calculate everything we want.

Unfortunately I just got hammered with stuff to do (I'm at work), so I haven't had time to put together the actual Queries yet. I'll get to that once I have a little more free time, but it looks like it will be at least a few hours.

[Rawb]

Alrighty! Sorry about the wait, but here's what I came up with:

Query 1 - This Query is where the user enters the SAN and Aggregate names to filter on and the date range to use. This Query returns a Record for every entry that matches the user input (ideally multiple Records). Now, there are a few assumptions here. The first is that the Start Date happens BEFORE the End Date - If the dates are switched, your "number of days" will be negative :P. The second assumption is that your Used value will INCREASE over time. If you have a lower Ending Usage, you'll probably generate a black hole and kill us all (i.e.: don't to eet!). Or you'll just get an error. . .

SELECT 
  Table1.*, 
  DateDiff("d",[Start Date],[End Date]) AS [Days In Range] 
FROM 
  Table1 
WHERE 
  [Table1].[Date Entered] BETWEEN [Start Date] AND [End Date] AND 
  [Table1].[SAN Name]=[SAN] AND 
  [Table1].[Aggregate Name]=[Aggregate]
ORDER BY 
  [Table1].[Date Entered];

The Fields [Start Date], [End Date], [SAN], and [Aggregate] are "Virtual Fields." That is, the system doesn't know what they reference, so it will ask the user. That's how we allow for user input in straight Queries!

Query 2 - References Query 1's results and lets us "do maths" on them to get the numbers we want. The only assumptions made here are basic ones: That your dates are date datatypes, and that all the assumptions of Query 1 are true.

SELECT 
  First(Query1.[Amount Used]) AS [FirstOfAmount Used], 
  Last(Query1.[Amount Used]) AS [LastOfAmount Used], 
  Last(Query1.[Amount Total]) AS [LastOfAmount Total], 
  Last(Query1.[Days In Range]) AS [LastOfDays In Range], 
  Last([Amount Used])-First([Amount Used]) AS [Amount Changed], 
  (Last([Amount Used])-First([Amount Used]))/Last([Amount Total]) AS [Percent Changed], (Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range]) AS [Time Left] 
FROM 
  Query1;

You'll notice that in both of these Queries, my Field names don't quite match what you used. It should be a fairly simple matter for you to change them to whatever you need.

Let me know if you have any questions about what the Queries do or how the specifics of "the maths" works!

[dr4ke]

Good morning!!

Your brain clearly works in the mornings, unlike some of us :-) !!!! Thank you for this.

It all looks perfect, apart from 1 thing I must ask! You mentioned the below assumption (which is a fair one to make!), but this may not always be true. The point in this exercise is to highlight the growing areas, report on them, and resolve them before time runs out. This basically means that if a disk is growing and growing to it's hearts content, some work will have to be taken to decrease it's size so it doesn't just run out of space and keel over!

The second assumption is that your Used value will INCREASE over time.

I hope you're not shouting several swear words at the screen right now

Dr4ke

[Rawb]

Heh, no problem. That's actually not a very hard fix, I just did it that way originally to make the "maths" a little more follow-able. To allow for a DECREASE (or no change) over time, simply make this replacement:

(Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range]) AS [Time Left]

With this!

IIf((Last([Amount Total])-Last([Amount  Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range])<=0,-1) AS [Time Left]

This will do a check and, if the beginning value of Used is larger than the ending value, it'll just print "-1" for the days remaining. Viola! No more black hole!

[Rawb]

Holy I'm-still-asleep Batman!

Just ignore my previous post plz!

Try this one instead:

Replace:

(Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range]) AS [Time Left]

With:

IIf((Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range])>0,(Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range]),-1) AS [Time Left]

[dr4ke]

Aha thank you :-) Asleep now, but awake first thing... You're one of those 'morning types' !?

Just to double check... The code will now work whether it's an increase OR a decrease? :-)

[Rawb]

Yes, once you make the substitution I posted above, the code will work for both an INCREASE in Used and a DECREASE.

Also, here's a slightly updated version of the original Query I posted (Query 2) to make the substitution a little easier. Just change the line immediately before "FROM".

SELECT 
  First(Query1.[Amount Used]) AS [FirstOfAmount Used], 
  Last(Query1.[Amount Used]) AS [LastOfAmount Used], 
  Last(Query1.[Amount Total]) AS [LastOfAmount Total], 
  Last(Query1.[Days In Range]) AS [LastOfDays In Range], 
  Last([Amount Used])-First([Amount Used]) AS [Amount Changed], 
  (Last([Amount Used])-First([Amount Used]))/Last([Amount Total]) AS [Percent Changed], 
  (Last([Amount Total])-Last([Amount Used]))/(Last([Amount Used])-First([Amount Used]))*Last([Days In Range]) AS [Time Left] 
FROM 
  Query1;

And no, I most certainly am NOT a morning person. My idea of a good morning is one that I've slept through. I just can't turn off my brain sometimes, so if it gets going on something, it'll wake me up whether I want it or not

[dr4ke]

Thank you very much :-)

Fair enough! Well it's the weekend soon so make sure you sleep properly. I've managed to get my hands on a Microsoft Access book that i'm going to work through to learn some more ... Although it is 2003 - most of the principals should be the same; just need to find where they are in the ribbon