TimeXCount(Number)

D

Dr. C

Hell-O,
I'm back. I need to find out how can I on a Report that I have designed
from a Query, where I have a Count Number Field that counts each Occurrence
of an Item and calculates the time used by that Item. Now I need a Field
(Unbound) that will take the Calculated time and multiply it by the Number in
the Count Field. I.E. I have 2 AR that each use a Machine for 5 Hours and 26
Minutes, now I need the Total time for those two ARs I.E. 10 Hours and 52
Minutes. And I would also like a Total Time field that adds all of the Summed
time into one field. Hope this is not to difficult. Thanks in advance.


DR. C.
 
J

Jeff Boyce

In case you hadn't run into this before, a word of caution on "time"...

The Date/Time field in Access records "point-in-time" data, not "duration".

If you need to keep duration data, you'll need to use one of the numeric
types -- from your example, you'd need to convert to total minutes and store
that.

I don't understand what an AR is. I'm having trouble visualizing your
underlying data structure. I'm not sure what query you've run and what data
it is returning.

Are you saying you want a total AND a total (Summed)? How are these
related?

More info, please...

Jeff Boyce
<Access MVP>
 
W

Wayne Morgan

To do what you're wanting, you need to do all of your calculations in
minutes (the smallest unit you've mentioned) then format that to display
hours and minutes in the textboxes. If all of the values are minutes, then
the multiplication and addition become very easy.

For example, 5 hours and 26 minutes is 326 minutes. Multiply by 2 gives 652
minutes. Convert this to hours and minutes for display gives 10 hours and 52
minutes.

To format minutes to display as hours and minutes in the textboxes:
=Minutes \ 60 & ":" & Format(Minutes Mod 60, "00")
 
D

Dr. C

Jeff and Wayne,
Yes I discovered how Access handles Time, that's why I run the following
code to calculate the amount of time a User uses a System:
DateDiff("n",[TimeIn],[TimeOut])\60 & ":" &
Format(DateDiff("n",[TimeIn],[TimeOut]) Mod 60,"00")

Now the AR is actually irrelevant as it is only a Designator in a Field in
a Table that represents a group of Users (Army in this case). I have a Time
in a Time Out a TotalUsed (Calculated by the Above Code) and a Count field
(Sum of a Number field representing the Number of Users in my 4 Groups (I.E.
AR as one). Now I need to Total the Number of Users in Each Group (Access
does this by the amount of Time for Users) I need a Total Count for Each
Group of Users, and I need a Total Time for each of the Groups.

Hope this helps in explaining what I have and what I need as a result for
the last two Fields (I.E. Total Count of Users by Group and the Total Time
Each Group Uses the System)

Thanks in Advance,
DR. C
 
W

Wayne Morgan

You can get the total number of users per group by placing a textbox (i.e.
txtUserCount), hidden if desired, in the report's detail section, set its
Control Source to =1 and set its Running Sum property to Over Group. In the
Group Footer, place a textbox with its Control Source set to =[txtUserCount]
in the detail section.

To get the sum of time, you would do the same thing, but the Control Source
for the textbox totaling the time would be the time field or calculation you
currently have. If you have a textbox in the detail section that already
shows the time value you want to sum, just set this textbox's Control Source
to the textbox you already have.

--
Wayne Morgan
MS Access MVP


Dr. C said:
Jeff and Wayne,
Yes I discovered how Access handles Time, that's why I run the following
code to calculate the amount of time a User uses a System:
DateDiff("n",[TimeIn],[TimeOut])\60 & ":" &
Format(DateDiff("n",[TimeIn],[TimeOut]) Mod 60,"00")

Now the AR is actually irrelevant as it is only a Designator in a Field in
a Table that represents a group of Users (Army in this case). I have a
Time
in a Time Out a TotalUsed (Calculated by the Above Code) and a Count field
(Sum of a Number field representing the Number of Users in my 4 Groups
(I.E.
AR as one). Now I need to Total the Number of Users in Each Group (Access
does this by the amount of Time for Users) I need a Total Count for Each
Group of Users, and I need a Total Time for each of the Groups.

Hope this helps in explaining what I have and what I need as a result for
the last two Fields (I.E. Total Count of Users by Group and the Total Time
Each Group Uses the System)

Thanks in Advance,
DR. C

Jeff Boyce said:
In case you hadn't run into this before, a word of caution on "time"...

The Date/Time field in Access records "point-in-time" data, not
"duration".

If you need to keep duration data, you'll need to use one of the numeric
types -- from your example, you'd need to convert to total minutes and
store
that.

I don't understand what an AR is. I'm having trouble visualizing your
underlying data structure. I'm not sure what query you've run and what
data
it is returning.

Are you saying you want a total AND a total (Summed)? How are these
related?

More info, please...

Jeff Boyce
<Access MVP>
Click to expand...
Click to expand...
 
Top