Simple MAX Query?

  • Thread starter Thread starter dlw
  • Start date Start date
D

dlw

I have a table of transactions with item#/date/amt, there
are repeating item#'s. I want a query that gives the
latest date and amt for each item number. It works if I
just have the item# on Group By, and the date on Max, but
as soon as I put in the amt, it gives me all the items and
dates, not just the one record with the latest info.
Sorry, this isn't very clear.
 
Dear DL:

It's another case of the need for a correlated subquery:

SELECT [item#], [date], amt
FROM YourTable T
WHERE [date] = (SELECT MAX([date]) FROM YourTable T1
WHERE T1.[item#] = T.[item#])

Substitute the actual name of your table where it says YourTable.

To learn about the techniques used here, the key words are "subquery"
and "alias".

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks, I'm kind of a menu jocky, so I just split it up
into two queries. Is that functionality so difficult to
put into the "totals" option? Like, have your Group By,
Sum, Max, etc, and then one other option that's just
Inclued, or something like that to get other fields?
Wouldn't that be a better mouse trap?
 
Dear DL:

The subquery is "correlated." That's what is on the last line where
it says:

WHERE T1.[item#] = T.[item#]

This allows the subquery to return a different "most recent date" for
each item found.

I don't see what you propose in terms of making it two queries.
Perhaps you are thinking of this:

SELECT [item#], MAX([date]) AS RecentDate
FROM YourTable
GROUP BY [item#]

Save the above as Q1.

SELECT [item#], [date], amt
FROM YourTable T
INNER JOIN Q1
ON Q1.[item#] = T.[item#] AND Q1.RecentDate = T.[date]

That would work. However, for someone who reads SQL, having to load
Q1 to find out what's goin on means this is less well documented
(IMHO). I'm not sure what the performance differences may be.

I also have a personal preference not to proliferate the number of
queries more than I must. Systems I build have hundereds of them as
it is.

So, this is not a "better mouse trap" for my money, but it is a viable
alternative, and depends on your own preferences.

In either case, you can add other columns from YourTable as needed.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top