SQL Group by Query with aggregate function as well as detail information

20 pts.
Tags:
GROUP BY statement
MAX
Microsoft Access
Query
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    see this article
    8,350 pointsBadges:
    report
  • msi77
    Maybe this will help.
    1,670 pointsBadges:
    report
  • TomLiotta
    We can't do homework directly. Show us the query that gives errors and we can help explain why they happen. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following