Calculating Average length of stay

thpus2 · May 14, 2004

Hey,
Well i have to create a query that will enable me to make a report displaying the average length of stay of the dogs i have in my dog kenel database.
I know this would need to be done using my booking table which holds all the information on what dogs i have staying and what length of time.

The table has the Fields:
booking_no
animal_no
pen_no
vet_visit_no
start_date
end_date

I'm new to access and i'm not sure how i would do this.
I need to someone take the start_date and end_date and calculate the average. Which i can then display in a report.
Any help would be much appriated
Thanx
Thpus

Calvin · May 14, 2004

use the DateDiff function to determine the amount of time and usually setting it to return minutes is best practice as it is easier to convert back to hours and days as needed. You can use DateDiff in a query comparing the two fields on the fly and outputing the minutes into a third field.

thpus2 · May 14, 2004

Ok so using the datediff function

OK,
So in creating this third field would i have to add the field the table like other fields. Or can you do this in the query design. Also what would the expression be like for datediff() would i have it in both start date and end date criteria.
And how would that output be put into the third field.
Would i then use this query in another query to create an average of all the records.
I'm sorry but i've never done these sorts of queries before.
Thanx
Thpus

Calvin · May 15, 2004

First lookup DateDiff in Access' help files for how to use the function or search this forum, there's hundreds of examples. Second, no you don't have to create a new field in the table. just a third column in the design of the query.
In your query design add the start date and stop date columns and in the thrid column you will add something similar to the following:

Code:

 Minutes: DateDiff("n",[StartDate],[StopDate])

With [StartDate] and [StopDate] being the names of the respective fields.

thpus2 · May 15, 2004

Access has no help on DateDiff.
I'm probably been really stupid but i created that third field used your code with my fields and it won't except that has code it complans about the colon.
Also is the n a varible used from the datediff in the other two colums or do i not put any criteria in them just in the third.
Also is how is this averaging all the records of time?
Thanx
Thpus

Mike375 · May 15, 2004

Try this.

In a created field

NewField:[End_date]-[Start_date]

That will give the answer in number of days, at least in Access95

If the two date field are results of Now() then the answer will be in number of hours. If you have a separate time and date field and do

[StartDateField]+[StartTimeField] you will get the same as Now() which is
15/05/2004 12:18:27 PM

If you did for your field
([EndDateField]+[EndTimeField])-([StartDateField]+[StartTimeField])

That should give you number of hours

You get the average from the whole column, that is the column (Field) where your numeric result comes. Do a query on the query and on the calculated field you Group by Avg

Jon K · May 15, 2004

Mike375 said:
If you did for your field
([EndDateField]+[EndTimeField])-([StartDateField]+[StartTimeField])

That should give you number of hours

Subtracting StartDateTime from EndDateTime returns only a double precision number, with the integer portion representing the number of days elapsed and the decimal portion representing the time elapsed. To get the number of hours from it, you have to do some calculation using the Int() function and the DatePart() function.

Mike375 · May 15, 2004

This what I get from the follwing

(Now()+1.1)-Now()
1.09999999999854

and ((Now()+1.1)-Now())*10
10.9999999999854

With 4 records Group by Sum gives for each

4.39999999999418 and 43.9999999999418

How does that look?

Mike

Mike375 · May 15, 2004

To get Age Next birthday I get the same result from the following:

With CL DOB being date of birth field

AgeNB: ((Date()-[CL DOB])/365.25)+1
Anb: Int([AgeNB])

and also

AgeNB: (DateDiff("yyyy",[CL DOB],Now())+Int(Format(Now(),"mmdd")<Format([CL DOB],"mmdd")))+1

You can waste some time on this stuff

Mike

Jon K · May 15, 2004

Mike375 said:
This what I get from the follwing

(Now()+1.1)-Now()
1.09999999999854
...........
How does that look?

Let's try to find out what it is in the Immediate Window:

Code:

WhatIsThis=(Now()+1.1-Now())

? Int(WhatIsThis) "day " & Format(WhatIsThis,"h ""hr"" n ""min"" s ""sec""")
[b] 1 day 2 hr 24 min 0 sec[/b]

? "That is " & Int(WhatIsThis)*24+DatePart("h",WhatIsThis) & " hours " & DatePart("n",WhatIsThis) & " minutes."
[b]That is 26 hours 24 minutes.[/b]

Hence the double precision number 1.09999999999854 that you got represented 1 day 2 hr 24 min 0 sec

So when you added 1.1 to Now(), you actually added 1 day (represented by the integer 1) and 2 hr 24 min 0 sec (represented by the decimal 0.1) to it.

0.1 is one-tenth of a day and is equivalent to 2 hour 24 minutes.

Date/time arithmetic can be very interesting because we must use some functions to interpret the results.

Calculating Average length of stay

thpus2

New member

Calvin

Registered User.

thpus2

New member

Calvin

Registered User.

thpus2

New member

Mike375

Guest

Jon K

Registered User.

Mike375

Guest

Mike375

Guest

Jon K

Registered User.

Similar threads

Users who are viewing this thread