Given a simple table with historical info about Hotelroom occupancies such as name of the guest, date in, type of room, number of nights, ...
I want to make a query resulting in 1 output record that shows the highest number of nights someone has ever stayed in the hotel, as well as the corresponding detail information: who was this customer and which type of room was he in?
Using the 'group by' and 'max' function it's easy to solve the first question, but I am not able to show the corresponding detailed information in that record (person's name, type of room).
I've done some experiments with a nested query in the 'expression' field, also trying a selfjoin but always getting errors. I'm confident there must be a solution? Can anyone help me out? Thank you!
Software/Hardware used: Access 2010 - Windows Vista