How to Max One Field in a Query and Return Same Record Data on Other Fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

doyle60@aol.com - 07 Dec 2007 21:45 GMT

This is a bit confusing so let me give an example. If I have the
following data in a table:

PO Style Date Store
100012 2012 10/31/05 Adam's
100012 2012 10/15/05 Zac's
100012 2012 12/10/06 Ralph's
100012 2012 12/10/06 Pete's
100015 3333 02/31/07 Macy's Co.
100015 3333 03/15/07 Bloomingdales
100015 3333 03/18/07 Joe's Tavern

how do I get it to return the style with the latest date and give me a
store that actually corresponds to that date? So, I want it to
return:

100012 2012 12/10/06 Ralph's
100012 2012 12/10/06 Pete's
100015 3333 03/18/07 Joe's Tavern

If there is a tie on the store (as Ralph's and Pete's is above), I
will take both.

You see, if I simply choose in the query to give the max date and the
max or min or first or last Store, I could end up with a mismatch. So
choosing last store with a max date gives this:

100012 2012 12/10/06 Zac's
100015 3333 03/18/07 Macy's Co.

Both of which are mismatches. Zac's never corresponds with 12/10/06.

I know how to do this in multiple queries and have been doing it for
years. But I thought there may be a way to do it in ONE query. Is
there?

Thanks,

Matt

Reply to this Message

Marshall Barton - 07 Dec 2007 22:53 GMT

>This is a bit confusing so let me give an example. If I have the
>following data in a table:
[quoted text clipped - 18 lines]
>If there is a tie on the store (as Ralph's and Pete's is above), I
>will take both.

[]

>I know how to do this in multiple queries and have been doing it for
>years. But I thought there may be a way to do it in ONE query. Is
>there?

You need to use two queries, but one can be a subquery.
Here's one way:

SELECT table.*
FROM table
WHERE table.date = (SELECT Max(X.date)
FROM table As X
WHERE X.style = table.style)

Signature

Marsh
MVP [MS Access]

Reply to this Message

Klatuu - 07 Dec 2007 22:55 GMT

This requires a subquery. Notice the Where Clause.

SELECT store, Po, Style FROM SomeTable
WHERE [date] = (SELECT MAX([date]) FROM SomeTable);

Also, it would be a good idea to change the name of the date field to
something other than date. Date is a reserved word and can cause problems.

Note, if this is a verly large table, it will take a while because it has to
run the subquery for each record in the main query.

Signature

Dave Hargis, Microsoft Access MVP

> This is a bit confusing so let me give an example. If I have the
> following data in a table:
[quoted text clipped - 35 lines]
>
> Matt

Reply to this Message

Gary Walter - 08 Dec 2007 12:10 GMT

I wonder if since the subquery is not correlated
(like Marshall's), if Access is not smart enough
to realize this needs to run only once?

> This requires a subquery. Notice the Where Clause.
>
[quoted text clipped - 48 lines]
>>
>> Matt

Reply to this Message

doyle60@aol.com - 11 Dec 2007 14:34 GMT

Thanks for all the comments here. I'd rather do two queries than do a
subquery. It's just easier for me to "see." If I have to go back and
review or edit my work, I can much easier understand a query tree than
edit a query with a subquery.

Thanks again,

Matt

Reply to this Message