highest value in query (1 Viewer)

[qaccess]

I have 3 fields in query. I want the highest value from field2 in field3. Shown as below: How I do this without using Group By?

field1 field2 field3
1 1 2
1 2 2
2 1 3
2 2 3
2 3 3

 

[neileg]

Calculated field, like this

NewField: IIF(Field1 > Field2, Field1, Field2)

 

[qaccess]

Still a problem

Sorry but I think this not what I mean...Below is a query with ID and field1 from a table Test. field2 will be calculate with a formula

[ID]![Test]-[field1]![Test]-field2

1-1-----3
1-2-----3
1-3-----3
2-1-----4
2-2-----4
2-3-----4
2-4-----4


how can i the get highest value of field1 [in this case 3 and 4] put in field2...I know how to do this with Group By and Max...But I don't want to use this...

 

[neileg]

Sorry, but I think this is a 'fix the solution' question. Without understanding the underlying process, it's not possible to solve the real problem. You have got part way down a route to the answer and now are stuck. The proper answer may be to go down a diufferent route.

The constraint about not using an aggregate query makes matters worse, because you don't explain why. To get a maximum value you have to use an aggregate operation. Now that's either in an aggregate query, or it's using DMax() (which is less efficient than the query) or you have to implement your own sort in code (which doesn't seem sensible).