find dates for outages

[timotai]

This is very complex and so Im not quite sure how to describe it properly so forgive me if its not understandable and please feel free to ask any questions.

All comments and ideas are greatly appreciated.

I am creating a database to capture outages on our computer systems. The way it works is that for every outage we enter into the database the start date, start time, end date & end time and a load of other info which is not applicable to my question.

What i want to do then is to run a report based on any date range, the user will give a start and end date for the report to show. It uses another table to show the potential availability, however what I want to do is show actual availablity.

This is where I am having problems, I need to get the amount of hours the system was unavailable for, if it was. what needs to be taken into account is the following scenarios:

1.) the date range I am reporting on is within the outage start and end dates

2.) the outage start and end dates are within the reporting dates

3.) the outage dates run on after the reporting end date and so I don't need to include all the outage time in the report only what i am requesting on the report

4.) the outage dates start before the reporting start date and so I don't need to include all the outage time in the report only what i am requesting on the report

I have, as i have mentioned, a table which shows system avilablity. this display the potential hours for each day, so for example if the system was down for 3 days you could get the number of hours per day from this table. However if it was a 1/2 day involved, e.g. the system came up at 12:00, or 13:00 then these hours need to be worked out a different way.

The potential hours table is structured as the following:

System ¦ Monday ¦ Tuesday ¦ Wednesday ¦ Thursday ¦ Friday ¦ Saturday ¦ Sunday

The number of hours the system is available for on these days is displayed in the fields below the days.

If any of my tables need to be altered i don't mind.

I consider my self very lucky if I get any help with this one as it is probably very hard to understand.

Thanks at least for taking the time to read it through and think about it.

Many Thanks All

Tim

 

[ETID]

Maybe the trouble lies in the way that your table is laid out.

If you structure your data in a more 'normal' manner...the tools that are built in to excel or access can make reporting much easier.

consider this table layout

System|Start_Date|Start_Time|End_Date|End_Time|

Then you can use the weekday([Start_Date]) function to return the day of the week from the Start_Date, etc... for the pupose of reporting.

 

[SkipVought]

and...

the potential hours table
[tt]
System|Day Of Week|Useable Hours
[/tt]
or
[tt]
System|Day Of Week|Normal Start|Normal End
[/tt]

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[timotai]

Thanks for the help, but i can't do that as the system is not up the same time every day. I have however got a solution to my problem by use of loops in code. I haven't put all the code in yet but have set up the loops and queries required. I am having a slight problem though. When I run the code Access is telling me Loop without Do. I have checked the code and for every loop there is a DO and vice versa. Can someone take a look and tell me whats wrong cause I just can't see it!

Function SysAvReport(Sdate As Date, edate As Date)
Dim db As DAO.Database
Dim rst, rsta, rstb As DAO.Recordset
Dim StrSql, StrSqla, StrSqlb As String
Dim rdate As Date
Dim pav As Double
Dim rday As String

'Loop through each system to get potential av & actual av & work out percentage

StrSql = "SELECT Supported_Systems.System as sys FROM Supported_Systems;"
Set db = CurrentDb
Set rst = db.OpenRecordset(StrSql, dbOpenSnapshot)

Do Until rst.EOF

    rdate = Sdate
    pav = 0

    Do Until rdate = edate + 1

        rday = Format(rdate, "DDDD")

        StrSqla = "SELECT System_Availablity.System, System_Availablity.[" & rday & "] as av " & _
        "FROM System_Availablity where System_Availablity.System = '" & rst("sys") & "';"

        Set db = CurrentDb
        Set rsta = db.OpenRecordset(StrSqla, dbOpenSnapshot)

        pav = pav + rsta("av")

        rdate = rdate + 1

    Loop


    rdate = Sdate

    Do Until rdate = edate + 1

        StrSqlb = "SELECT Outages.System, Outages.Start_Date sdate, Outages.Start_Time stime, " & _
        "Outages.End_Date edate, Outages.End_Time etime, Outages.Total_Outage_Time " & _
        "FROM Outages WHERE (((Outages.System)='" & rst("sys") & "') AND ((Outages.Start_Date)>=#" & rdate & "#) " & _
        "AND ((Outages.End_Date)<=#" & rdate & "#));"
        Set db = CurrentDb
        Set rstb = db.OpenRecordset(StrSqlb, dbOpenSnapshot)

        If rst.EOF Then

        Else

            Do Until rst.EOF

                If rdate = rstb("sdate") And rstb("edate") Then
                End If

                If rdate = rstb("sdate") And rdate <> rstb("edate") Then
                End If
        
                If rdate = rstb("edate") And rdate <> rstb("sdate") Then
                End If

                If rdate > rstb("sdate") And rdate < rstb("edate") Then
                End If

                rst.MoveNext
            Loop

            rdate = rdate + 1
    Loop

    End If

    rst.MoveNext


Loop



End Function

 

[timotai]

Don't worry, I found it