469,341 Members | 7,194 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,341 developers. It's quick & easy.

How to find avg. between two dates

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).
Nov 20 '10 #1
9 5589
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.
Attached Files
File Type: zip sample.zip (24.8 KB, 323 views)
Nov 20 '10 #2
NeoPa
32,182 Expert Mod 16PB
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.
Nov 20 '10 #3
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
Expand|Select|Wrap|Line Numbers
  1. dim days as integer
  2. days=DateDiff("d", startdate, enddate)
  3. txtdays.SetFocus
  4. txtdays.Value = days
  5.  
Nov 20 '10 #4
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.
Nov 20 '10 #5
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)
Nov 20 '10 #6
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.
Nov 20 '10 #7
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
Expand|Select|Wrap|Line Numbers
  1. the onclick event for btnstartdate:
  2. startdate.value=calendar1.value
  3. the onclick event for btnenddate:
  4. enddate.value=calendar1.value
  5.  
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.
Attached Files
File Type: zip room usage app.zip (105.7 KB, 236 views)
Nov 21 '10 #8
Hi Malcolm,

Thanks alot for all your help. It worked perfectly. You have been great. Thanks again.
Nov 22 '10 #9
NeoPa
32,182 Expert Mod 16PB
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.

Expand|Select|Wrap|Line Numbers
  1. SELECT   [Room Type]
  2.        , Avg(DateDiff('d', [Departure Date], [Arrival Date]) + 1) AS [AvgDays]
  3. FROM     [YourTable]
  4. GROUP BY [Room Type]
  5. 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).
Nov 22 '10 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

22 posts views Thread by mike | last post: by
4 posts views Thread by Matt Hamilton | last post: by
17 posts views Thread by Justin Emlay | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Purva khokhar | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.