Find the max value (not as simple as it sounds)

[snipe]

Ok so I have a query that has 3 fields: Package, Package Component, and Retail Price of that package component.

I am trying to do a group by that yeilds 1 record for each package, has the most expensive component, and the price of that component.

I sorted the query by package (ascending), and price (descending).

I built a query on top of that one, and did a group by on package, first on component, and first on retail price.



I do not get the value that I should.

I am kind of stumped on this what seems like a very simple query to make.

Any thoughts or suggestions?

EDIT: I am doing this on a remote server, so I am on Access 2003, not 2010.

[JoeM]

There are a few ways to do this.

One way is to use two queries.

First Query
Create an Aggregate Query that only has two fields, Package and Price.
Group by Package, and return the Max Price value.
This will return the maximum Price for each Package.

Second Query
Link your first query back to your original table on both the Package and Price fields.
Return all three fields from your original table.
This should give you what you want.

Note that you can also do all this in a single query, if you use subqueries or nested queries.

[snipe]

Wow. I tried this and linked only the price field and it gave me all sorts of wacky results. Didn't think about linking both fields.

Thank you so much!