Query issue

[tedstriker]

Hello,

I need to create a query that has me stumped. I have a single table of data containing hotel room types, arrival dates and departure dates. Their are 3 types of rooms, Ocean view, side view, and dining view. I need to create a query to display the average length of stay per room type. I'm lost on how to solve this one. Any ideas? Thanks in advance, I look forward to your replies.

[ItsMe]

Have you tried building a query object using the Query Builder? Perhaps the Avg() function with an alias is the solution. Check out the Totals option within the ribbon while in Design View of your query object.

[tedstriker]

I have, but I'm really confused as to how to differentiate between the different room types within the same column. How to I get the average for just one type of room?

[ItsMe]

If you are including the room type in your query and the avg is averaging all three types together, you will need to have three different queries then. Another approach may be to use Grouping in a report. You can group the room types there and use an expression in the Group Footer, employing the Avg() function in your unbound text box.

EDIT:
I just did a quick test and I am not sure where it is you are having trouble. Take a look at Query1 here.

[NTC]

create "Query1" that has fields: hotel room types & length stay (departure minus arrival)

then create "Query 2" and drag onto it Query 1 as its record set table; then make Query 2 into an aggregate query by pressing the sigma symbol (looks sort of like an E) - - this will default to 'group by' for both fields ; leave the room type as group by but change the length stay to be Average...


it is actually quite easy....