Auto-increment fields

[Robyne]

I have a dbase that tracks help desk calls. The CallNumber field - first 4 characters to represent the current year, next 2 are the current month, the last 3 are the sequential number of calls that month. We're moving from Alpha/5 (which has this capability) to Access. I can't figure out how to calculate the field to increment 1 from the previous record. And allow me to change the beginning number when the month changes.

 

[BNPMike]

The idea that a field actually contains other fields (in your case the id also holds the year/month it was created) is a bit non-relational. The 1st Normal Form bans this.

Access will not automatically generate field values of this type. You can generate the year/month element yourself. The incrementing number needs to come from an SQL statement that returns the highest number already in use this month. You also have the problem that for the first record of each month you will have no current highest number.

Another problem typically encountered is in multi-user scenarios two people can get the same next number so whoever does their save second has their transaction rejected due to duplicate id. Thus people tend instead to have a separate table of numbers. New helpdesk calls first get a number and then proceed to load data for that number.

Frankly I would be inclined to generate the numbers using eg Excel and load them into your id table. You can then mark them as they are used. To get a new number just do a select to return the lowest unused number.

 

[Craig0201]

Robyne,

Mike is indeed correct.

Further to his answer, what would happen if 1000 calls were received in a month?

To get the number isn't actually that hard.....

CallNumber = Format(Date(),"yyyy&quot & Format(Date(),"mm&quot & Nz(Format(DMax(Right([CallNumberFieldName],Len([CallNumberFieldName])-6)),"YourTable", Left([CallNumberFieldName],6) = Format(Date(),"yyyy&quot & Format(Date(),"mm&quot,"000&quot,"001&quot

The first two parts get the year and month and the last long bit gets the call number....

But be VERY careful of the 1000 records per month......even if you think it's possible, it may be in a few years......remember the Y2K problem!

Craig