if you do a min/max domain query you find the value of the relevant item

is here anyway of determining which row access actually selects - so that you can get other information from the row - puting expression in the totals query doesnt solve this, and i cant see an easy way.

what i have is data structured into zones, each zone having a value, but some of the values are equal.

so

zone 1 - 100
zone 2 - 80
zone 3 - 100

i have a set of data including details from numerous zones. if i do a query to find the highest value zone of my active selection, it returns 100 - but i need to know whether this was zone 1 or zone 3. is there any way to achieve this if the dataset contains both zones 1 and 3, it doesnt matter which I get - but if the dataset only contains zone 3, say, then i need to know that the 100 it has picked relates to zone 3, not zone 1

If done in a query you include the zone with the Max. And then to limit the records you link both the zone and the field so you get the max for that zone. Does that help, or did I misunderstand something?

If I had understood Leigh Purvis, I once saw him remarked that was one case where use of First() or Last() is legitimate. So if you did a Min() you can use First/Last function to 'select' the key of the row you want to see. Of course, if there's two rows that has same value satisfied by Min() or Max(), the result may not what you expect.

If there is more than 1 row First gives the key of the first and Last gives the key of the last, no surprise there then.
I don't really understand Dave's post, he wants to know the zone if there is only1, but doesn't want to know if there are more!

Brian

i'll try the first and last - that may work

here's a detailed example - i have five deliveries into zones as follows

item 1, zone 1
item 2, zone 4
item 3, zone 1
item 4, zone 4
item 5, zone 3

now we charge customers based in zones

so
zone 1 = 100
zone 2 = 120
zone 3 = 110
zone 4 = 120

so i have a query, joining my items to the zone table, and find the highest value zone amongst these deliveries, with a dmax - this gives me one row, with a value of 120 - which we can see by inspection is zone 4

now i want to find that zone that the dmax, but with a query so i can reuse it - but if i do a dlookup on the 120, i actually get zone 2 returned - whereas my data set included zone 4 and not zone 2. On the other hand if i include the zone in the dmax query, as a group by, then it changes the nature of the query

thats the nature of the problem

if my data contains both zone 2 and zone 4, then i dont mind which answer i get, zone 2 or zone 4 - but i dont want the answer being zone 2, if my data doesnt include zone 2

and i've just tried first/last but that doesnt help either.

Dave:

This is an example, but perhaps it will help.

I first made a query to pull the max order date:

SELECT Max([OrderDate]) AS MaxOrderDate
FROM Orders;

and then used that query inside another one:

SELECT Orders.CustomerID, Orders.OrderDate
FROM Orders INNER JOIN MaxOrderDate ON Orders.OrderDate = MaxOrderDate.MaxOrderDate;

So you would do something similar.

doesnt work bob -

in your example, its not just the maxorderdate i'm after, its some other data from the same row on which the orderdate was retrieved to identify exactly which row was selected - which becomes a problem therefore if there is more than one row with that same orderdate.

Dave:

Can you upload a stripped down version with just the tables and some bogus data? I would like to play with it but I don't want to try to create the whole thing from scratch.

Dave,

Don't know how you are handling the output but could you not do it in two stages?

First get the DMax() value then get a recordset based on the dmax value

xMax = DMax("[Field]","Table")

Set Rs = db.OpenRecordset("Select * From Table Where Field = " & xMax)

Then you can play with the contents.

Just a suggestion.

David

As I said before this is the bit I don't get

if my data contains both zone 2 and zone 4, then i dont mind which answer i get, zone 2 or zone 4 - but i dont want the answer being zone 2, if my data doesnt include zone 2

How can the answer include zone2 if its not in the data? I must be missing something

With Bob's solution the rest of the data is retrieved in a 3rd query, if its that data which may not contain the zone2, ie omit that line from your example, then inculde that table in the 2nd query jiuned to the first table on zone.

Brian