UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Short Time Calculation, Access 2003    
 
   
jdfellows
post Nov 27 2017, 10:41 AM
Post#1



Posts: 277
Joined: 5-November 07
From: Wisconsin


Hello,

I am trying to do a calculation in a query on the following and can not get it displayed properly:

AmountOfEmployees1 (Number) = 2
TimeOfEmployees1 (Short Time) = 15:28 (hh:mm)

This should result in 30:56 but my query result is 30.93. My expression is as follows: [AmountOfEmployees1]*[TimeOfEmployees1]*24.

Thank you in advance and any help would greatly be appreciated.
Go to the top of the page
 
GroverParkGeorge
post Nov 27 2017, 10:49 AM
Post#2


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


Without seeing the actual values in the underlying tables, it's hard to guess, but here's one possibility.

Doing math on "dates" is always risky, because there are actually two different ways we can talk about it.

First, there are "point in time" values, such as "2017/11/27" which is the current date, and "07:46AM", which is the current time on my computer's OS. It can be formatted as a "Short Time" because it is a Point in Time value.

However, there are also "elapsed time" or "duration" values, such as your 15:28, which is NOT a short time, but a count of hours and minutes that have elapsed.

The problem can be that you need to use ONLY elapsed times, or durations here, not "Short Times".

Also, depending on the underlying values themselves, you may be encountering rounding errors.

So, let's step back and look at what is involved at the table level.

Thanks.

--------------------
Go to the top of the page
 
projecttoday
post Nov 27 2017, 11:01 AM
Post#3


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


Please explain how you get 30:56 from 2 X 15:28 X 24.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
jdfellows
post Nov 27 2017, 11:14 AM
Post#4



Posts: 277
Joined: 5-November 07
From: Wisconsin


projecttoday: The time should show 30:56 (hh:mm) whereas 15:28 (hh:mm) times 2 employees should result in 30:56 (hh:mm). If I do not multiply by 24 it results in 1.288

GroverParkGeorge:
Below is my table definition

Field Name Data Type
AmountOfEmployees1 Number (Double)
TimeOfEmployees1 Date/Time (Short Time)
Go to the top of the page
 
projecttoday
post Nov 27 2017, 12:26 PM
Post#5


UtterAccess VIP
Posts: 8,689
Joined: 10-February 04
From: South Charleston, WV


I'm still more confused.

You can't multiply a date/time field. You will have to use numerical fields instead.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
GroverParkGeorge
post Nov 27 2017, 12:28 PM
Post#6


UA Admin
Posts: 31,245
Joined: 20-June 02
From: Newcastle, WA


Yup, that's what I said. You are not working with times here, you are working with Durations. Both can be formatted to LOOK like the same thing, but they are not.

Store start and stop times as times.

Convert the elapsed times between those start and stop times to minutes. Then you can do the math on those minutes.

--------------------
Go to the top of the page
 
doctor9
post Nov 27 2017, 03:42 PM
Post#7


UtterAccess Editor
Posts: 17,924
Joined: 29-March 05
From: Wisconsin


jdfellows,

Pardon me for jumping in... can you explain your table design a bit?

> AmountOfEmployees1 Number (Double)
This seems to be a count of how many people performed some sort of task. The Double datatype is meant for gigantic numbers or extremely tiny fractions, using a floating decimal point. If the values in this field are always whole numbers and are always less than 32,000 you should use the Integer datatype instead.

> TimeOfEmployees1 Date/Time (Short Time)
This appears to be a length of time. Your calculated field seems to be trying to figure out the total number of "man hours" used up for a certain task. You may want to consider storing this as an Integer as well, specifically the number of minutes. Your data entry form can still allow the user to enter a two-digit number, a colon, and another two-digit number, but you would use VBA to convert this string into a number of minutes for storage. This way you can easily multiply the number of people by the number of minutes to get the total minutes. Divide this by 60 to get the number of hours, and the remainder is the number of minutes. Do you want to display 40 hours and 30 minutes as 40:30 or as 8 Days and 30 minutes?

Finally, the presence of the number "1" at the end of each field name kind of implies that there are other similarly-named fields in your table like "AmountOfEmployees2". If this is the case, you should stop what you're doing and Normalize your data. This sort of table design is known as a Repeating Group and more often than not causes more problems than it solves. For example, if you do have a field named AmountOfEmployees2 and it's some sort of "second shift for the same task" sort of thing, you should have a separate table for the count of employees and the total time worked.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:23 PM