Selecting only one record for a max of in a design query

[PhilChamberlain]

Hi there, hope someone can help..



I want to select only one record when using a max of aggregation. e.g. if I have year, item, price and I want to find the most recent price of every item my instinct is to the aggregation to MaxOfYear and GroupByItem but I can't do anything with price. If I use GroupBy it will give me a new record for every change in price so I won't get a unique one record per item result. In theory I would want an option called something like 'passive' so it just gives me which ever record obeys the instructions of the other two aggregations, but this doesn't exist. It seems so simple i feel i must be missing something. I hope this makes sense. I am only working in design mode.


Many thanks

Phil

[recyan]

Assuming,
The Item & The Year together are unique in your table (myTable),
try joining
the aggregate query that you have described
to your same Table (myTable)
with
the join on, perhaps something like below :

SELECT
..............
FROM
qry
INNER JOIN
myTable
ON
(qry.MaxOfTheYear = myTable.TheYear)
AND
(qry.TheItem = myTable.TheItem)

Just see if it works.

Thanks

[PhilChamberlain]

thanks recyan,

I just about understood what you meant. (I am in design mode not SQL). I've tried it and it works, so job done.

thanks again

phil

[recyan]

Glad you found it helpful.

Thanks