Avg time

[Noel]

I am trying to figure the average time between time in and time out. I want
to show the avg mintues a person had to wait. I am drawing a blank as to how
I enter the code.

 

[John W. Vinson]

I am trying to figure the average time between time in and time out. I want
to show the avg mintues a person had to wait. I am drawing a blank as to how
I enter the code.

Create a query based on the table with a calculated field. In a vacant field
cell type

WaitTime: DateDiff("n", [time in], [time out])

This will give the number of minutes for that record; make it a Totals query
and average this field.

 

[Jerry Whittle]

In a query:

TheMinutes: Int(CCur([paid_date]-[invoice_date])*1440)

I'm assuming that you want whole minutes and not decimals. That's why I used
the Int function.

You're probably wonder what's up with the 1440. Well there's 1440 minutes in
a day. That was easy!

Now for the CCur. The following returns what you would expect.

Debug.Print Int(CCur(#3/1/2006 1:00:00 AM#-#2/19/2006#)*1440) = 14460

But look at this:
Debug.Print Int((#3/1/2006 1:00:00 AM#-#2/19/2006#)*1440) = 14459

It's a minute off. It's due to a common floating point math problem with
computers. If we remove the Int function, you can see it.

Debug.Print ((#3/1/2006 1:00:00 AM#-#2/19/2006#)*1440) = 14459.9999999965

The Int and Fix functions round down and even this little difference can
drop a minute every once in a while.

Even this simple example can show it.
Debug.Print 3.1 - 3.11 = -9.99999999999979E-03 which is not the same as
-0.01.

Therefore I added the CCur function to change the data type to currency. The
Currency datatype doesn't have the floating point problem!

 

[Noel]

How do I average that whole column? Basically, get an average of them all
together.

I am trying to figure the average time between time in and time out. I want
to show the avg mintues a person had to wait. I am drawing a blank as to how
I enter the code.

Create a query based on the table with a calculated field. In a vacant field
cell type

WaitTime: DateDiff("n", [time in], [time out])

This will give the number of minutes for that record; make it a Totals query
and average this field.

 

[John W. Vinson]

How do I average that whole column? Basically, get an average of them all
together.

I don't know, since you haven't posted any description of your table or of
what you want to average.

Give us a little help here?

 

[John W. Vinson]

In a query:

TheMinutes: Int(CCur([paid_date]-[invoice_date])*1440)

Interesting. Why not DateDiff("n", [invoice_date], [paid_date]) which returns
an integer number of minutes in one step?

 

[Noel]

I want to show the difference of time between "Arrival Time" and "Time In" in
one column but then I want to be able to get the average time of that column.
I am wanting to see what the averge wait time was.

 

[John W. Vinson]

I want to show the difference of time between "Arrival Time" and "Time In" in
one column but then I want to be able to get the average time of that column.
I am wanting to see what the averge wait time was.

To answer THAT LITERAL QUESTION - which I'm guessing is not the answer you
want:

Create a query based on your table.
Include *only* one field in the query by typing

WaitTime: DateDiff("n", [Arrival Time], [Time In])

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M).

Change the default Group By on the Totals row to "Avg".

Open the query. You'll get one row showing the average wait time across your
entire table.

 

[Noel]

I tried this and nothing shows up in my query. I entered WaitTime:
DateDiff("n", [Arrival Time], [Time In]) in the "Field" box of the query in
design, clicked on the Greek Sigma, and then selected Avg on the total drop
down box. Saved it and then opened it and no data shows up.

I want to show the difference of time between "Arrival Time" and "Time In" in
one column but then I want to be able to get the average time of that column.
I am wanting to see what the averge wait time was.

To answer THAT LITERAL QUESTION - which I'm guessing is not the answer you
want:

Create a query based on your table.
Include *only* one field in the query by typing

WaitTime: DateDiff("n", [Arrival Time], [Time In])

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M).

Change the default Group By on the Totals row to "Avg".

Open the query. You'll get one row showing the average wait time across your
entire table.

 

[John W. Vinson]

I tried this and nothing shows up in my query. I entered WaitTime:
DateDiff("n", [Arrival Time], [Time In]) in the "Field" box of the query in
design, clicked on the Greek Sigma, and then selected Avg on the total drop
down box. Saved it and then opened it and no data shows up.

Please open your query in design view; select View... SQL from the menu; and
post the SQL text here. Also indicate the datatype (they *are* Date/Time
values... right??) of [Arrival Time] and [Time In], and post a sample record
or two.