How to find avg. between two dates

[sharon pandher]

i need to find avg. time a client is spending in a certain room type in a hotel. could someone please help me how to go about it. Table fields are room type, arrival date and departure date.

** Edit **
To clarify, the average required is the average no of days for each room type. Client's don't come into the equation at all (See posts #5 & #7).

[malcolmk]

try subtracting arrival date from departure date to get days stayed, store in table then do the usual average calc (total days divided by number of entries).
Here is a sample form showing how to do the calculation.

[NeoPa]

MalcolmK:
, store in table then

Malcolm, we appreciate your taking the time to respond to this question, but please be very careful before suggesting saving away any data which is already determinable from existing data. This is entirely against the concept of noramalisation (See Database Normalisation and Table structures) which is possibly the most important tenet of RDBMS work.

Other than that point I would say Malcolm probably has the question answered, though its in an attachment rather than in the thread, so it's hard to tell.

[malcolmk]

Neopa, comments noted. I failed to read the post properly and failed to note data already entered, just suggested how to get a date diff.

I assume that the client id is not relevant ?
So are you saying that you want to select a room type from maybe a drop down list and then display the average for that room type (on that form or in a report)?
What about start and end dates for the query? for instance do you want to be able to select the period that the average is calculated on? weekly, monthly, quarterly? I think the best option is select a start date and an end date for the query. I also assume that this form is seperate to the initial entry form or is it a subform?

As for code, retrieve startdate and enddate from table where roomtype=?(whatever type)
days diff is calculated as follows and displays amount of days in textbox txtdays

dim days as integer
days=DateDiff("d", startdate, enddate)
txtdays.SetFocus
txtdays.Value = days
 

[sharon pandher]

Thanks for the quick reply Malcolm,

Client id is not relevant. There are 3 room types and all they want to know is the average length of stay per room type. Other fields in the table are arrival date and departure date. We were told to keep it basic but its taking too much time and i cant figure out how can i keep it simple and basic. The table is already filled with information and all i need to do is be able to create the report. So arrival date and departure dates are already filled in for all the room types.

[malcolmk]

Hello Sharon, thing is if you just run a query without start and end for the query it will read the whole table of data, maybe 12 months worth or even a couple of years if you been inputting that long. how often you want to run report? best to have a start end date for query so for instance , average stay in standard room for Nov or average stay jan-mar (1st quarter)

[sharon pandher]

Hi Malcolm,

The table i have been provided with only has 30 entries. For this assignment all they are looking for is a report that would give them the average length of stay per room type. I have attached the table for your review.

[malcolmk]

Hi Sharon, I don't see any attachment above? I knocked up a simple but complete app for you i the attached.
I will edit this post and discuss coding asap but as you seemed to be in a hurry posted the app.
========= explanation =========
Seemed to me that the room types should be stored in a table so allowing adding removing, modifying differing room types/grades without getting into any code blocks

The report screen form contains a combo box bound to the roomtype table. There are two more unbound text boxes to hold start and end dates for the report period. The easiest way to handle entering the date was to place a calendar control on the form and a setdate button for both the start date(btnstartdate) and end date (btnenddate) of the report

the onclick event for btnstartdate:
startdate.value=calendar1.value
the onclick event for btnenddate:
enddate.value=calendar1.value
 

selecting report options sets hidden textbox reptype to a value of either avguseroomtype (average use for room type specified only)or avguseall (include all room types in report)
Selecting to preview the report calls a first query which uses the results of a second query.
The first query takes the values roomtype, startdate, enddate from the unbound fields on the form and places them in the criteria field of the query,then looks up the corresponding values in table roomdata

Forms!reports.controls!roomtype.value
>=Forms!reports.controls!startdate.value
<=Forms!reports.controls!enddate.value

The query also contains an expression field to calculate the days between the start and end dates.
DateDiff("d",startdate,enddate)

The second query simply takes the output days from query number one and calculates an average.

The report calls query two then sums and formats the data for presentation.
The query used for all room types is the same as the first except that the criteria box for room type is left blank.

[sharon pandher]

Hi Malcolm,

Thanks alot for all your help. It worked perfectly. You have been great. Thanks again.

[NeoPa]

Sharon, I've just updated the original question to indicate what you mean as expressed more clearly in posts #5 & #7. This will help experts and searchers alike, to follow the thread.

Fundamentally (and I know Malcolm has provided something specific to your requirement, but I want to leave something visible to all, which answers the fundamental question - particularly with other searchers in mind) you need a query that determines an average duration per room type. I'm not going to complicate the proposed solution by worrying about any rounding of days etc, although that is perfectly possible, using various different rules as to how to determine what is rounded up and what down, as this would detract from the fundametal nature of the solution. DateDiff() is clear what it's doing and likely to be adequate in most cases anyway.

SELECT   [Room Type]
       , Avg(DateDiff('d', [Departure Date], [Arrival Date]) + 1) AS [AvgDays]
FROM     [YourTable]
GROUP BY [Room Type]
ORDER BY [Room Type]

Malcolm's point about filtering the records based on a date range may also be pertinent to you (and worth considering, in which case I'd recommend checking just the start or the finish date. If there is a strict date window where everything not included would be ignored, the checking can, indeed, get somewhat more complicated).