[AccessD] SQL Question

Are you returning one row for each possible date_start_appntmnt?  Leave
that out of the query, except in the MAX and don't group by it either.

You may need to do two separate queries, one for stdnt_nbr,
max(date_start_appntmnt) and another for
[stdnt_nbr],[pos_crse_cd],[prsn_nbr].  Then you'd do a join of the two
result sets to get your desired single row.  


Liz 


Liz Doering
elizabeth.j.doering at wellsfargo.com
612.667.2447


"This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation"

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Martin Reid
Sent: Friday, May 11, 2007 9:21 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL Question

Running hte following returns three rows. I need it to return asingle
row. Is there something missing. This is SQL Server 2005. The WHERE is
ismply used for testing to restirct this to a single student.,
 
SELECT
[stdnt_nbr],[pos_crse_cd],[prsn_nbr],date_start_appntmnt, MAX (
[date_start_appntmnt] ) FROM [s335_stdnt_staff] WHERE [stdnt_nbr] =
XXXXXXXXXXXXXXXXXX GROUP BY [stdnt_nbr],[pos_crse_cd],[prsn_nbr],
date_start_appntmnt

 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974465