FUNCTION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

angie - 29 Mar 2006 12:49 GMT

i have a query that consists of various fields among which i want to
manipulate the following:

i have a unique field (ID) for which i have multiple entries on the
following fields:
DATE & AMOUNT. i want the query to return the amount corresponding to the
latest date for each id.
i have created a totals query and i have selected the maxDATE which is
working properly for the DATE field. then i have selected the max or last
AMOUNT but it returns the maximum amount, not the amount corresponding to the
maximum date i seek for.

could you help me?

Reply to this Message

John Spencer - 29 Mar 2006 13:42 GMT

You can do this in two queries.
Save your first query minus the Maximum amount field
Create a new query with your table and the saved query. Join the ID to the
ID and the Date to the Max Date. Add the Amount in from the table.

You can also do this in one query. That would look something like the
following in the SQL view of a query.

SELECT [ID], [Date], [Amount]
FROM [YourTableName]
WHERE [Date] =
(SELECT Max(T.[Date])
FROM [YourTableName] as T
WHERE T.ID = [YourTableName].ID)

>i have a query that consists of various fields among which i want to
> manipulate the following:
[quoted text clipped - 10 lines]
>
> could you help me?

Reply to this Message