highest number in a column

[Guest]

I have a field in an Access database I created for a nonprofit that each time
a person rents a video the data looks like this :
1abc
2abc
3abc
1def
2def

The number increases each time they rent a video, the letters represent the
agency renting the video. The number will be a running total.
What the nonprofit wants to know which agency has the highest number in that
month.
In the sample above the formula would be looking for 3abc.
I realize that there is a lot of flaws in the logic for this, but this is
what they want.

 

[Al Campagna]

Bill,
You'd be better off creating 2 fields, and concatenating those fields to
"display" to the customer. While the user sees "1abc", you really work
with...
(example only)
PurchaseCount = 1
and
StoreCode = "abc"

= PurchaseCount & StoreCode

This way, you'll find it easier to query and/or control the increment of
PurchaseCount for each customer.

 

[Guest]

Thanks Al I understand what you suggested, then what formula do I use to find
the highest number

 

[Larry Daugherty]

Look in Access Help for "DMax()". You'll want to apply it something
like

MyFieldInTable = DMax(MyFieldInTable) +1

HTH

 

[Al Campagna]

Bill,
As Larry suggests in his post, a DMax should do the job.
However, the Where portion of the Dmax would have to include...
(use your own field names)
CustID = the CustID on the new record AND
StoreCode = StoreCode on the new record

 

[Guest]

Thanks guys
--
Bill

Bill,
As Larry suggests in his post, a DMax should do the job.
However, the Where portion of the Dmax would have to include...
(use your own field names)
CustID = the CustID on the new record AND
StoreCode = StoreCode on the new record
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."