Solved

Find Max Value MS Access 2003

Posted on 2011-02-16
2
334 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

726 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