Determine totals based on another field. 1

[NFLDUser]

I have a table with a field called pat_num for our patient numbers. Another field is called cost.
I want a way to figure out the pat_num with the highest total cost.

I guess I want to find the patient that has spent the most money.

Any ideas?

 

[PHV]

You may try something like this (SQL code):
SELECT pat_num, cost
FROM yourTable
WHERE cost = (SELECT MAX(cost) FROM yourTable)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

Try:

SELECT TOP 1 PAT_NUM, Sum(Cost) As TotalCost
FROM [table with a field]
GROUP BY PAT_NUM
ORDER BY Sum(Cost) DESC;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

with the highest total cost
OOps, disregard my previous post :~/
Duane's suggestion should cover the issue.

 

[NFLDUser]

Thanks ... that worked like a charm.