Percent_Total Down Time: [Total Down Time]/1440


Sum Of Total Down Time: Sum([SiebelOutageQuery].[Total Down Time])


>=#01/01/2004# And <=#08/02/2004#


I have a somewhat complicated somewhat simple question...

I am running a database that contains server uptime, server downtime and totaldowntime, etc.

I already have one query that creates the minute total of downtime (by figuring from the start of the downtime to the start of the uptime - that gives me my total number of time the server was down).

Now, what I want to do is create another query off of that query that can give a TotalDownTime percentage based off of the total minutes the server was down. Now, figuring that it goes by minutes, I created this:

Percent_Total Down Time: [Total Down Time]/1440

- This essentially divides Total Down Time (which is in minutes already) by 1440 (how many minutes are in a day) to get a percentage value of how long it was down for that day. Now, I also have a second field (Date) which I have basically defined with the following criteria: >=#01/01/2004# And <=#08/02/2004# (essentially giving me a YTD figure percentage).

here is where my problem is at. I can create the above query, but now I obviously need just 1 final percentage which equals my YTD Total Down Time. I had did the above expression, and in the Totals field, did AVG for the Total Down Time and then AVG for Date, to get one single value - but it doesn't appear to be correct.

Is there a better way to get the final percentage total in this query?

If you were going to use this in a report or form, it'd be a snap to calculate in the report or form itself....

I will be in a report...

the problem is, how do I actually figure it correctly?

I have tried different things and it always comes up with a different percentage and I can't tell what is right or wrong?

See what it needs to reflect is this:

Say we are reporting the avg percentage of downtime in the month of May from operating hours of 7am to 7pm when the system is actually being used?

31 days
12 hours a day
it's down the follow times:

22 minutes - May 3rd
1 hr 53 minutes - May 17th
6 minutes - May 24th
34 minutes May 29th

I know how to break down each individual day - I would divide by 720 which is how many minutes are in an actual working day (7am to 7pm) right?

I have gotten that far and I have percentage values for the above right? Well I can't seem to find a way to get an actual sum and/or average of those to say "For the month of May, the system has been down xx.xx% during operating hours of 7am to 7pm."

See what I mean? And another thought comes to mind, even if I got an average of those percentages - would it still be an accurate reflection for the whole month? It's is really only an average percentage of down time, but not average percentage of downtime vs. the entire month...

See what I mean?

OK so the Total UpTime is actually a calcuation I do or is it one that I can do in Access?

The fiels I have in the query are as follows:

Field: Percent_Total Down Time: Avg([Total Down Time]/720)
Total: Expression
Show: X

Field: Date
Table: OutageQuery
Total: Group By
Show: _
Criteria: >=#5/20/2004# And <=#6/30/2004#

Field: SlowOrDown
Table: OutageQuery
Total: Group By
Show: _
Criteria: Like 'Down'

I deleted that post, because I hadn't thought your issue out completely. Your TotalTime might be your 720, if the op hours never change. You may still aggregate that constant as if it were calculated, though.

Let's back up a second.

You have a date/time it went down and a date/time it went back up. What happens if there is no downtime in a day? No records?

Exactly...no records if there was no downtime...

As for the down records, they do each have a date (inputted once on the form when originally inputting records) and a downtime and uptime...

Okay, here goes:

Get rid of the TotalDownTime field and add two fields in your query, both expressions:

DailyTotalTime:720

PeriodTotalTime:720*(DateDiff(#5/20/2004#,#6/30/2004#)-1)

I would replace the hard dates with variables, either user defined variables in the criteria section, unbound control values on a form or fields in a Report Parameters table. Then you don't have to change the expression every time you change the date range.

If operating hours aren't 7 days per week, you would need to make a function to calculate out the number of operating days in the reporting period.

In your report, Daily Downtime % would be =[SloworDown]/[DailyTotalTime] and total Downtime percent would be sum([DailyDownTime])/[DailyTotalTime]

when entering PeriodTotalTime:720*(DateDiff(#5/20/2004#,#6/30/2004#)-1) it stays I have the wrong number of arguments

sorry
DateDiff("d",#5/20/2004,#6/30/2004#)-1

I think I have confused myself...

Specifically, what should my query contain?

And yes, its only 5 days not 7 days

If it's 5 days per week, you need a custom function to return the number of workdays. You can also exclude holidays in this custom function. I don't have time to work out the function at this moment, and I don't have the sample code for it, but there's a great sample floating around UtterAccess somewhere.

The fields you would want are your date field, your DownTimeField, a calculated expression for DailyTotalTime, and a calculated expression for PeriodTotalTime.

You can't calculate the PeriodTotalTime until you have the custom function worked out. If you want to do it as a hard constant until then, you can have an expression field PeriodTotalTime:21600.

The 21600 is the number of minutes between 7 and 7 in your sample date range. It would be 20880 if you don't count May 31 (Memorial Day).

So in your report, in the detail line, you have Date, SloworDown, and a calculated Control, =[SloworDown]/DailyTotalTime for the downtime percent for the day.

In your Report Footer section, you would have =Sum([SloworDown], and a calculated Control, -Sum([SloworDown])/[TotalDownTime]