Click here to Skip to main content
11,486,023 members (76,819 online)
   

Database

 
AnswerRe: SSRS 2005 Pin
Mycroft Holmes6-Dec-12 17:57
memberMycroft Holmes6-Dec-12 17:57 
GeneralRe: SSRS 2005 Pin
berba6-Dec-12 19:06
memberberba6-Dec-12 19:06 
GeneralRe: SSRS 2005 Pin
Mycroft Holmes6-Dec-12 22:43
memberMycroft Holmes6-Dec-12 22:43 
QuestionSQL Group By Question [modified] Pin
loyal ginger6-Dec-12 6:27
memberloyal ginger6-Dec-12 6:27 
I am trying to do this in Microsoft Access. I seem to be unable to construct a 'simple' query. To illustrate, here is a sample table:

Field1	Field2	Amount 
A	New	10
D	Old	5
G	Old	1
H	Old	15
H	New	55
X	Old	20
Z	Old	100
Z	New	20

I need to group by Field1 and only return the Field2 data for the record that has the maximum number in the Amount field. I tried to use the 'First' function but Microsoft Access sort the records internally so it does not return the right value even if I pre-sort the records with a sub-query. Do you have any advice? The result should look like the table below.

Desired Result:
		
Field1	Field2	Max(Amount)
A	New	10
D	Old	5
G	Old	1
H	New	55
X	Old	20
Z	Old	100
I have trouble getting the value of Field2 in the record containing to the maximum value of 'Amount.'

Any suggestions? Thanks in advance for your time!

modified 6-Dec-12 11:51am.

AnswerRe: SQL Group By Question Pin
Blue_Boy6-Dec-12 11:11
memberBlue_Boy6-Dec-12 11:11 
GeneralRe: SQL Group By Question Pin
loyal ginger31-Dec-12 6:48
memberloyal ginger31-Dec-12 6:48 
QuestionIssues installing ODP.NETx64 Pin
cpp_prgmer6-Dec-12 5:18
membercpp_prgmer6-Dec-12 5:18 
AnswerRe: Issues installing ODP.NETx64 Pin
Eddy Vluggen6-Dec-12 15:37
memberEddy Vluggen6-Dec-12 15:37 
GeneralRe: Issues installing ODP.NETx64 Pin
Peter_in_27806-Dec-12 16:12
memberPeter_in_27806-Dec-12 16:12 
GeneralRe: Issues installing ODP.NETx64 Pin
Mycroft Holmes6-Dec-12 17:52
memberMycroft Holmes6-Dec-12 17:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.


Advertise | Privacy | Mobile
Web02 | 2.8.150520.1 | Last Updated 22 May 2015
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid