calculate average time (1 Viewer)

[Mike375]

Mike - You're absolutely correct! Problem being that KidRobot isn't sharing what the field values represent and is tap-dancing with descriptions of formats, but not telling us the underlying field values. So, we're left to guess. I don't understand -- what's the deal? (Realize that many of you fellow-responders are much more sympathetic than I. Conversely, I think the OP needs to provide some valuable info and quit tap-dancing.)

Bob

Bob, like you I have often wondered what the secret is in some postings. It can't be economy of typing as it ends taking 10 times as many postings

 

[raskew]

Mike -

I'm not even going to open-up your attachment. If RobotKid can't tell us the underlying value of his table, we've got a serious problem.

RobotKid -

Please come on-line and tell us something better than the format of your mysterious field.

Bob

 

[kidrobot]

Mike -

I'm not even going to open-up your attachment. If RobotKid can't tell us the underlying value of his table, we've got a serious problem.

RobotKid -

Please come on-line and tell us something better than the format of your mysterious field.

Bob

I'm at work now!!!! attached is a sample of data for ONE day. How can I correct average this!

Here is the query I am using to try to average this...

SELECT Avg(rickdata.[TOTAL ELAPSED TIME]) AS [AvgOfTOTAL ELAPSED TIME], Calendar.week_of_year
FROM rickdata INNER JOIN Calendar ON rickdata.Date = Calendar.calendar_date
GROUP BY Calendar.week_of_year;

 

[raskew]

OK -

What you've attached is an Excel spreadsheet. Please upload the comparable Access table.

Bob

 

[The_Doc_Man]

The problem is simple. Date/Time fields have baggage. They SUCK as a means of doing time manipulation. As said earlier, there is a Date/Time field but no Time (only) field. But you can still make this fly better if you understand that what you are REALLY storing is a DOUBLE representing units of DAYS (not hours...). You are essentially taking the average of FRACTIONS. If the time is really just minutes and seconds, not very big fractions, either.

You can search this forum for recent postings on date/time formatting issues. I was in a thread just a day or two ago involving not the value of a date/time field, but how to FORMAT the date/time field. Your problem is that the data you want really IS in that field, but Access doesn't know how to format it for you because you want it in a format that Access considers non-standard.

KR, I'll add this TINY ping on you... if you had searched this forum FIRST, you would have found articles on the subject. Many regulars have posted functions in VBA or complex formatting strings to do exactly this type of formatting. You engaged us, and it is OK that you asked for help. But you could have had your help even earlier if you just had searched first.

I also understand deadlines and such, but be aware that you are about to fall into a trap. Depending on forums (fora?) to do your job or schoolwork means you never learn how to really use the search resources available here and at other sites. In my humble opinion, you are asking for trouble down the road if you don't learn to do at least some of this searching for yourself. If it is REALLY urgent and no one happens to be online who has a clue about your problem, what would you do then?

Sort of like the old USA Wild West gunslinger, learn to search first and ask questions later?

 

[kidrobot]

OK -

What you've attached is an Excel spreadsheet. Please upload the comparable Access table.

Bob

sample Access DB is attached!

 

[kidrobot]

The problem is simple. Date/Time fields have baggage. They SUCK as a means of doing time manipulation. As said earlier, there is a Date/Time field but no Time (only) field. But you can still make this fly better if you understand that what you are REALLY storing is a DOUBLE representing units of DAYS (not hours...). You are essentially taking the average of FRACTIONS. If the time is really just minutes and seconds, not very big fractions, either.

You can search this forum for recent postings on date/time formatting issues. I was in a thread just a day or two ago involving not the value of a date/time field, but how to FORMAT the date/time field. Your problem is that the data you want really IS in that field, but Access doesn't know how to format it for you because you want it in a format that Access considers non-standard.

KR, I'll add this TINY ping on you... if you had searched this forum FIRST, you would have found articles on the subject. Many regulars have posted functions in VBA or complex formatting strings to do exactly this type of formatting. You engaged us, and it is OK that you asked for help. But you could have had your help even earlier if you just had searched first.

I also understand deadlines and such, but be aware that you are about to fall into a trap. Depending on forums (fora?) to do your job or schoolwork means you never learn how to really use the search resources available here and at other sites. In my humble opinion, you are asking for trouble down the road if you don't learn to do at least some of this searching for yourself. If it is REALLY urgent and no one happens to be online who has a clue about your problem, what would you do then?

Sort of like the old USA Wild West gunslinger, learn to search first and ask questions later?

Thanks for your input. Trust me I went through this millions of times yesterday and it just got too frustrating. I searched on google and got a couple threads that helped guide me in the correct direction. I'll admit I did not search the forum, but with topics as broad as this it is hard to use a forum search.. I could search 'time' but I know there will be an extreme amount of irrelevant hits. I'll try and do a search now, but if you have links to any you found helpful please link me. I everyone for the help.

 

[raskew]

Doc-Man:

Did you happen to download the attachment? As stated, it's an Excel spreadsheet. I couldn't even make sense out of what it represented.

When formatted as a number, it appeared to maybe be the time-portion of an Access date/time field, but was unable to convert it to anything.

Your thoughts appreciated. -- Bob

 

[Mike375]

I make average time 12-28-02AM

 

[ChrisO]

Sub TestIt()
    Dim dblTotalDays   As Double
    Dim lngNumRecords  As Long
    Dim dblAverageDays As Double

    With CurrentDb.OpenRecordset("rickdata")
        Do Until .EOF
            dblTotalDays = dblTotalDays + CDbl(![TOTAL ELAPSED TIME])
            .MoveNext
        Loop
        lngNumRecords = .RecordCount
        .Close
    End With

    dblAverageDays = dblTotalDays / lngNumRecords

    MsgBox "Average Seconds = : " & dblAverageDays * 86400 & vbNewLine & _
           "Minutes         = : " & dblAverageDays * 86400 \ 60 & vbNewLine & _
           "Seconds         = : " & Int((dblAverageDays * 86400 / 60 - dblAverageDays * 86400 \ 60) * 60)
           
    [color=green]' Average time : 28 Minutes and 2 Seconds (truncated)[/color]

End Sub