Select Top Query

  • Thread starter Thread starter Rob M
  • Start date Start date
R

Rob M

Hi all,

I'm wondering whether someone can assist with a query.

I have a patient database with 3 tables:

1. Demographics
2. Referrals
3. Appointments

Each patient can have multiple referrals, and each referral can be
associated with multiple appointments.

I need a query that lists all patients, all referrals, and only the
earliest appointment for each referral. I can create a basic query
that generates the following...

Name ReferralDate AppointmentDate
John Smith Jul 15, 2009 July 23, 2009*
John Smith Jul 15, 2009 July 30, 2009
John Smith Sep 23, 2009 Oct 15, 2009*
Jane Doe November 15, 2009 December 1, 2009*
Jane Doe November 15, 2009 December 8, 2009
Jane Doe November 15, 2009 December 15, 2009
Barack Obama June 23, 2010 June 24, 2010*
Barack Obama July 1, 2010 July 9, 2010*
George Bush April 1, 2009 April 9, 2009*

....but how can I list only the lines with an asterisk? I know it has
something to do with SELECT TOP, but that's about it.

Many thanks,
Rob
 
Rob said:
Hi all,

I'm wondering whether someone can assist with a query.

I have a patient database with 3 tables:

1. Demographics
2. Referrals
3. Appointments

Each patient can have multiple referrals, and each referral can be
associated with multiple appointments.

I need a query that lists all patients, all referrals, and only the
earliest appointment for each referral. I can create a basic query
that generates the following...

Name ReferralDate AppointmentDate
John Smith Jul 15, 2009 July 23, 2009*
John Smith Jul 15, 2009 July 30, 2009
John Smith Sep 23, 2009 Oct 15, 2009*
Jane Doe November 15, 2009 December 1, 2009*
Jane Doe November 15, 2009 December 8, 2009
Jane Doe November 15, 2009 December 15, 2009
Barack Obama June 23, 2010 June 24, 2010*
Barack Obama July 1, 2010 July 9, 2010*
George Bush April 1, 2009 April 9, 2009*

...but how can I list only the lines with an asterisk? I know it has
something to do with SELECT TOP, but that's about it.
Click to expand...
No, it's a GROUP BY query you need. Not TOP. You nede to group by Name
(not a good name for a field by the way - it's a reserved keyword.
PatientName would be better - you can google for a list of Access
reserved keywords, but it's simplest to just be descriptive when naming
your fields) and ReferralDate, and use the aggregat Min() function to
get the earliest AppointmentDate. Turn on the Totals row in the query
grid by clicking the ? button in the toolbar. Just remember, if you have
a field in the grid and you're not grouping by it, you have to aggregate
it.
 
Thanks for your help! Your suggestions were really helpful. I'm
playing around with it now.

Also the field name is PatientName...I typed that in just for the
example.

Regards,
Rob
 
Back
Top