query that will select the max number

[jorbroni]

I am trying to write a query in access using data that gives me:

The names of my clients
each division under their name
The cash flow for each division.

What I am trying to do is write a query that only gives me the division for each client that has the highest cash flow out of the group. I tried setting the client Name column to "group by", the division column to "group by", and the cash flow column to "max". That did not work.


Could someone guide me in the right direction.

Thanks

 

[mph1]

I think you would be better of using a report to group rather then a query and the DMax function to obtain the greatest cash flow. An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[dodge20]

I think it is easier if you write it in SQL

select max(cash_flow), division from table
order by cash_flow, division

Then I think you can choose the option to hide the cash flow column if you want to. Dodge20
If it ain't broke, don't fix it.