[AccessD] SubQuery Help???

This gives me the results you specified based on the given data sample:

SELECT ID, [Date], Amount
FROM MyTable
WHERE Amount =
   (SELECT Max([Amount])
   FROM MyTable Sub
   WHERE Sub.ID = MyTable.ID)

In your earlier message, it sounded like you wanted to limit the data range
as well, in which case just add a Between clause to the main and sub
queries.

-----Original Message-----
From: Mark A Matte [mailto:markamatte at hotmail.com]
Sent: Saturday, January 24, 2004 3:55 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] SubQuery Help???


Sorry,

I wasn't quite clear...here is the data

ID  Date  Amount
a  2/3        4.5
a  2/4         5.8
b  2/3         3.7
b  2/4         2.5
a  2/6         3.8

The results I would like to see are:
ID  Date  Amount
a  2/4      5.8
b  2/3      3.7


Any ideas?

Thanks,

Mark



>From: Brett Barabash <BBarabash at tappeconstruction.com>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: "'Access Developers discussion and problem solving'" 
><accessd at databaseadvisors.com>
>Subject: RE: [AccessD] SubQuery Help???
>Date: Sat, 24 Jan 2004 15:08:36 -0600
>
>Howzabout:
>
>SELECT ID, Date, Amount
>FROM MyTable
>WHERE Date Between [A-Date] And [B-Date]
>AND Amount =
>   (SELECT Max([Amount])
>   FROM MyTable
>   WHERE Date Between [A-Date] And [B-Date])
>
>-----Original Message-----
>From: Mark A Matte [mailto:markamatte at hotmail.com]
>Sent: Saturday, January 24, 2004 2:17 PM
>To: accessd at databaseadvisors.com
>Subject: [AccessD] SubQuery Help???
>
>
>Hello All,
>
>I'm having a brain cramp...I have a table with 3 fields: ID, Date, Amount.
>I'm pulling the records between A-Date and B-Date...from these records I
>need the date where the Amount is greatest...can someone refresh my
>memory...
>
>Thanks,
>
>Mark
>
>---------------------------------------------------------------------------
-----------------------------------------
>This email and any files transmitted with it are confidential and
>intended solely for the use of the individual or entity to whom
>they are addressed.
>If you have received this email in error please notify the
>originator of the message. This footer also confirms that this
>email message has been scanned for the presence of computer viruses.
>
>Any views expressed in this message are those of the individual
>sender, except where the sender specifies and with authority,
>states them to be the views of Tappe Construction Co.
>
>Scanning of this message and addition of this footer is performed
>by SurfControl E-mail Filter software in conjunction with
>virus detection software.
>
>_______________________________________________
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com

_________________________________________________________________
Scope out the new MSN Plus Internet Software - optimizes dial-up to the max!

   http://join.msn.com/?pgmarket=en-us&page=byoa/plus&ST=1

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--------------------------------------------------------------------------------------------------------------------
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom
they are addressed.
If you have received this email in error please notify the
originator of the message. This footer also confirms that this
email message has been scanned for the presence of computer viruses.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

Scanning of this message and addition of this footer is performed
by SurfControl E-mail Filter software in conjunction with 
virus detection software.