Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Is there an alternative to max() for finding the last record?

Status
Not open for further replies.

chandler

MIS
Joined
Dec 10, 2000
Messages
66
Location
US
I have a table which uses an incrementing non-autonumber counter field that gets concactinated with the year portion of now() to generate a job number in text format (01-9999).
At the new year, the counter will need to reset to 0000. However, in my query to get the last record, I'm currently using max(counter) to get the last record inserted. If I manually set the counter to 0 in the last record, the max(counter) obviously won't see that as the last record and at the new year, the jobnumber will start at max(counter)+1 (which is around 2500 right now) instead of 0+1 Chandler
I ran over my dogma with karma!
 
hi Chandler

sounds like something that should have been thought of last year huh?
:)
I do this to myself all the time.

It seems that at least with an Access DB the Max function will get it correct.
That is if I'm following you in thinking the progression would go like:

01-1
01-2
...
01-9999
02-1
02-2
...
02-9999
03-1
Where 01 - 03 is the year.

If that is the case then Max should continue to work.
I would recomend seperating those fields so that you can have a correct auto-number with no concatination and also have a field with the year, You could concatinate them for the Job # like this:

Select FieldYear + '-' + FieldKeyID as JobNumber
(hmm.. someone correct me if those +'s should be &'s )

It's up to you but I think that would cause less problems.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top