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

[doyle60]

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

 

[Marshall Barton]

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. []
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)

 

[Klatuu]

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.

 

[Gary Walter]

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.

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.
--
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:

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

 

[doyle60]

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