Solved

Find Max Value MS Access 2003

Posted on 2011-02-16
2
331 Views
Last Modified: 2012-05-11
I am trying to find the max value in the Current column using ms access.
Table
ID    Name    Current
1      A           1
1      B           23
1      C           12  
2      A            22
2      B            9

So my end result with be the following:
ID  Name Current
1    B        23
2    A        22
0
Comment
Question by:asarda
  • 2
2 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34909961
select id, name, max(current) as maxcurrent
from tblname
group by id, name
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34910008
Fixed

select g.id, MIN(name) as [Name], g.[Current]
from
(
select id, max([current]) as [Current]
from tblname
group by id
) g inner join tblname t on t.id=g.id
where g.[Current] = t.[Current]
group by g.id, g.[Current]
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now