Highest Value

  • Thread starter Thread starter DougW
  • Start date Start date
D

DougW

This seems it should be simple, but here is an example and question.

Sample data:

Name Response#
------- ----------------
Jane 2
Jane 4
Steve 2
John 1
John 2
John 3

As this above simple example shows, I have a table with customer responses
to various mailings. Some customers respond to all mailings, others may
respond to only 1 of several mailings. I would like to extract only those
records with the one response (like Steve above) along with only John's
response 3 and Jane's response 4. In other words, I just want a listing of
customer responses, but only showing their "highest value" response.

Thanks.
 
Just to clarify, the resulting query would show the following records only:

Name Response#
------- ----------------
Jane 4
Steve 2
John 3


Thanks.
 
SELECT [NAME], Max([Response #]) as MaxResponse
FROM yourTable
GROUP BY [Name]

BTW, Name is a reserved word in Access. I would strongly recommend using
Reserved words as field names (or for anything else for that matter). If you
are going to use it as a field name, then I would strongly recommend that
everywhere you use it, you wrap it in brackets [ ].

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Thank you - that solved the problem for me. Regarding the use of "Name," I
only used for this example.

--
DougW


Dale Fye said:
SELECT [NAME], Max([Response #]) as MaxResponse
FROM yourTable
GROUP BY [Name]

BTW, Name is a reserved word in Access. I would strongly recommend using
Reserved words as field names (or for anything else for that matter). If you
are going to use it as a field name, then I would strongly recommend that
everywhere you use it, you wrap it in brackets [ ].

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



DougW said:
Just to clarify, the resulting query would show the following records only:

Name Response#
------- ----------------
Jane 4
Steve 2
John 3


Thanks.
Click to expand...
Click to expand...
 
cough i do think you mean i would strongly NOT recomment using
reserved words =P just to be annoying
 
Yes,

You are absolutely correct. My brain must have been going faster than my
fingers.

Dale
 
Back
Top