Getting the Highest Value from a Particular Field

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

Thanks in advance to any insight someone might be able to
lend. Hopefully, I can explain myself well here......

Let's say I have the following data in a table with fields
(NAME, POSITION and ACHIEVED DATE)
Don, Associate, 6/1/01
Don, Counsel, 7/11/02
Don, Director, 10/1/03
Kate, Associate, 8/1/01
Kate, Counsel, 7/11/03
Kate, Director, 10/1/03

I have a form with a text box, and I want to create a
query that shows everyones position as of a particular
date.

So if I enter 12/31/02 in this text box, I want to see
Counsel for Don (with the achieved date of course), and
see Associate for Kate.

I first tried to enter criteria in the Date Field
<=[TEXTBOXVALUE], but that of course will give MUITIPLE
VALUES for Don, which is what I don't want. I want the
MAX or HIGHEST value for each Name before the entry date.

I am somewhat at a loss here. I tried the GROUP BY but
could not find anything that worked. It looks as if maybe
I'll need VBA that cycles through a recordset and then
gets the last value? Can anyone lend any insight?

Thanks,
Don
 
Hi Don,

Are we talking about Word, here, or another application, such
as Access? If it's not Word, try asking in a group specific
to that application (this group is for MS WORD).

If it's Word, we need more information (such as what kinds of
fields you're using in this table).
Thanks in advance to any insight someone might be able to
lend. Hopefully, I can explain myself well here......

Let's say I have the following data in a table with fields
(NAME, POSITION and ACHIEVED DATE)
Don, Associate, 6/1/01
Don, Counsel, 7/11/02
Don, Director, 10/1/03
Kate, Associate, 8/1/01
Kate, Counsel, 7/11/03
Kate, Director, 10/1/03

I have a form with a text box, and I want to create a
query that shows everyones position as of a particular
date.

So if I enter 12/31/02 in this text box, I want to see
Counsel for Don (with the achieved date of course), and
see Associate for Kate.

I first tried to enter criteria in the Date Field
<=[TEXTBOXVALUE], but that of course will give MUITIPLE
VALUES for Don, which is what I don't want. I want the
MAX or HIGHEST value for each Name before the entry date.

I am somewhat at a loss here. I tried the GROUP BY but
could not find anything that worked. It looks as if maybe
I'll need VBA that cycles through a recordset and then
gets the last value? Can anyone lend any insight?
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep
30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail :-)
 
Back
Top