472,782 Members | 1,113 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,782 software developers and data experts.

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 5989
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, 353 views)
Nov 20 '10 #2
32,534 Expert Mod 16PB
, 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
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
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


The query also contains an expression field to calculate the days between the start and end dates.

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, 257 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
32,534 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

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

Similar topics

by: mike | last post by:
If I had a date in the format "01-Jan-05" it does not sort properly with my sort routine: function compareDate(a,b) { var date_a = new Date(a); var date_b = new Date(b); if (date_a < date_b)...
by: Jerry | last post by:
How do I find the dates of the Sunday and Saturday of a week if I have the week number of a selected year? Thanks! Jerry
by: Matt Hamilton | last post by:
I have a query that returns multiple dates and I want to find the average date... How can I do this? I tried to use the ToOADate() to get the total of the dates as a double, then divide by the...
by: Justin Emlay | last post by:
I'm hopping someone can help me out on a payroll project I need to implement. To start we are dealing with payroll periods. So we are dealing with an exact 10 days (Monday - Friday, 2 weeks). ...
by: newnewbie | last post by:
I have an Access database that I upload a data extract intoto daily. I want to create a query that will give me a list of dates that no data can be found for. E.g. there are no records created on the...
by: nirmalsingh | last post by:
hi all! i have to find the dates of sundays in a year. help me with sample code in C#. thanx in advance.
by: nirmalsingh | last post by:
hi all! I want to find dates of sundays in a particular year. help me with sample code. thanx in advance.
by: Zyronne | last post by:
Hello Experts. I have a database that deals with history. Since MSSQL 2000 cannot accept dates earlier than Jan 1, 1753 I converted my column to CHAR. My problem now is I cannot find dates on...
by: myemail.an | last post by:
If I need to format how the content of a field is displayed, I can click ALT + ENTER from design view, and specify the format, for example, the number of decimal digits and so on. Is there a way...
by: pchaitanya | last post by:
I have selected some list of valid dates to a label. now i need to find first day among the given dates from label contrl i got dates from calender control by clicking for entire week.. ...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.