Storing time value greater that 24 H

[arsys]

Hello,



Im new to access, i`ve been tasked to make a small database. I have a machine that stores how mutch its online and working. The value that it gives me r something like 774:34 ( the total time that the machine worked from its birth). Now, if i take that value every day i can determin how mutch did it work on the day before. I have an excel file where i have the cells formate [hh]:mm:ss ( where i can store that time value) but i want to make it in access for better control.
How do i make it in access so i can insert 1 colum ( 774:34 ), second colum ( 776:44 ) and 3rd colum will do colum 2 - colum 1 = 2:10 ( the functioning time of the machine)


Thanx in advanced.

[Bob Fitz]

How do i make it in access so i can insert 1 colum ( 774:34 ), second colum ( 776:44 ) and 3rd colum will do colum 2 - colum 1 = 2:10 ( the functioning time of the machine)

Short answer: You don't

Access is not a spreadsheet and I would advise against trying to make work like one.
I would suggest one table with three columns: ID as Auto-Number. RecDate as Date. Hrs as Integer
Record each days reading as a record.
Use a calculated field in a query to produce the result you require.
Do not try to save the calculation to a field. In Access calculations are normally done whenever and wherever they are required.

[CJ_London]

what does 774:34 represent? 774 hours and 34 minutes? 774 mins and 34 seconds? and how is it stored? as text "774:34"? a number 77434? or something else?

Agree with Bob access is not a bigger version of excel, it is a different animal completely. Trying to apply excel principles will end in tears.

it can be done quite easily in a query - but what that query looks like depends on understanding what the underlying data actually looks like i.e. without any formatting

[moke123]

I have a machine that stores how mutch its online and working.

I was wondering where the data is from. The machine or is it manually input?

[arsys]

Yes the number is 774 hours and 34 minutes. The machine is actualy a Locomotive that has a mini computer that registers fuel consumption and function time. Problem is that the time is cumulative. From the day the mini computer was mounted on the locomotive till present day... and it stores the time the locomotive functions ( hours and minutes ) not the stop hours. The thing is i tryed to insert in access table 774:34, but the only format that i can do that is text and canot do calculation. The readings r manualy imputed in access db

[CJ_London]

date/time are stored as doubles (excel, access,, any database) - the bit before the decimal point is the number of days since 31/12/1899 - so today is 44063 and the time is the number of seconds so far divided by 86400 (the number of seconds in the day - so now is 44063.9146990741. What you see as a date is just a format.

if you go over 24 hours the the day part increments by 1 and the time part starts incrementing from 0 in seconds.

This is why it matters what the underlying value actually is - so you are saying the user enters a value 774:34 which is text.

think you need a function to convert to datetime

Function convertToTime(strTime As String) As Date


    convertToTime = ((Split(strTime, ":")(0) * 60) + Split(strTime, ":")(1)) * 60 / 86400


End Function

suggest you use this after the user has entered the time to store this value insteadas well

744.34 converts to a date of 30th January 1900 00:34:00

Since you are only interested in the time difference, it doesn't matter what the date is.

so now you have this time value stored in a field in you table. Let's call the field truTime

And your query would be something like this. There are other ways but lets see if this works first

SELECT truTime, dMax("truTime","myTable","truTime<#" format(truTime,"mm/dd/yyyy hh:mm:ss") & "#") AS prevTime
FROM myTable
ORDER BY truTime

[ssanfu]

In another life, I had to take run time readings on machines that ran 24/7. we kept track of start times and end times.... so did the engineers and the mechanics. So each group had different run time durationes.
Each machine had a run time hours meter that had hours and 100's of an hour (443824.35 hours). We had record (paper) of "start times" (the meter reading) and the "stop times". Using the start and stop run time hour readings, we could calculate the "Run Time Hours" (between shutdowns).

Let's say Machine 1 had been shut down and we are now starting it. The "Run Time" meter reads exactly 443824.00. If Machine 1 runs for 3 days and 6 minutes, the run time meter should then read 443860.1. If you subtract the ending run time reading from the starting run time meter reading, you would get 36.1. That can be converted to 36 hours and 6 minutes. That is how long (the duration) the machine ran.

You would not and cannot write the duration as 36:06. Anything with a colon in it is a TIME, not a duration.
You can say I ran from 10:00am until 11:30am; so I ran 1 hour and 45 minutes or 1.75 hours. You CANNOT write it as 1:45 .


QUESTIONS:
1) Exactly how do you get the run time readings/hours?
2) Are you manually entering the readings?


For normalized table, you could/would have a table with at least:

An Autonumber PK field
a field to know which machine the readings belong to
a start time field for the hours
a start time field for minutes
a field to record whether it is a start or stop event.



For a non-normalized (a table designed like a spreadsheet), you could use

An Autonumber PK field
a field to know which machine the readings belong to
a start time field for the hours
a start time field for minutes
a end time field for the hours
a end time field for minutes.






OK, I'm off my rant..... I see so many examples of time written to express a duration I drives me Bonkers!
Breathe in, Breathe out........ repeat until you reach 99 years old.....




And welcome to the forum.......

[arsys]

@ssanfu
Hahahha .... Thanx for the welcome
I get the readings by phone each morning from the mechanic on the locomotive. He tells me the reading in thins format ex: 774 hours and 25 minute. I know from the previous day that the reading in the morning was ex: 773 hours and 05 minutes. So by subtracting i know that the locomotive ran yesterday for 1 hour and 5 minutes. I want to register this in a database, each mornig to insert only ex: 774 hours and 25 mins ( maibe in the format of 774:25 ) and and access to do the math (the subtraction) and store the ran time in a field. I dont need the time that the locomotive was stoped.
Also at the end of the mounth i need it to make a total of run time and a fuel consumption/run time ( but this is something else, will get to that later when i manage to store the run time properly ). Oh... the readings r inserted manualy in the table... maibe make a form to make it more user frendly.
Thanx for the help, i will try Ajax`s formulas to see if it works