Primary Key with date and increment

[xupz]

I'm not sure if it's a simple task, keep in mind I'm new to using access. We have a need at work for a small db just to keep track of some things and access seems appropriate.

I was attempting to set up the primary key in a date / increment format.

Basically the format would be year, month, day, increment (starting at 001)

060125-001
060125-002
060125-003

etc

next day I would like the key to change according to date and reset the increment to start again at 001

060126-001
060126-002

etc

I do realize they are different data types, I'm just not sure how to attack setting it up. Thank you in advance for any help / suggestions.

 

[Pat Hartman]

Having keys of this type is not advised. Just keep the date in a separate field and use an autonumber as the primary key. Mushing values together into a single field violates all of the first three normal forms.

 

[xupz]

I understand your point, the reason for doing it this way is really for statistical purposes, time series & frequency analysis.

So if combining the two isn't a good idea, is it possible to set the primary key as multiple fields in the table? My only issue with the autonumber is that I would prefer it to reset to 001 each day and increment sequentially.

 

[Pat Hartman]

A primary key or index may contain up to 10 columns. An autonumber cannot be reset though. You would need to write your own code to generate a sequence number. I don't understand the need for the sequence number. What does it do that an autonumber will not do? An autonumber will provide a unique identifier. It will also maintain record input sequence when it is set to increment.

 

[grubnz]

Hi Pat,

Hope all is well.

I was reading this and understand the three normal forms.

I have the Primary Key autonumber As the primary key to keep numbering consistent. And I have the entered date Field

Then because people like having Year-Seq Number(resets each year)

Would I then be able to concatenate this for peoples use only.To for fill peoples needs without breaking the three normal forms.

I hope that makes sense.

Thanking you for your time and assistance in this matter.

Have a wonderful day.

Ani

 

[The_Doc_Man]

If the company rules require a reset, that is one thing. If people like it but it makes no technical sense, that's another.

The typical way you do this, though, is not that hard.

1. Have two fields - yearnum and yearseq
2. Take a DCount( "[YearSeq]", "yourtablename", "[yearnum]=" & DatePart("yyyy", Now() )
3. Add one to the count. That becomes the new sequence number.
4. Write the new record with the current year and the new count.

Alternative in #2: After deciding that you have at least one entry for this year, you COULD do a DMax of the yearseq rather than taking a count, then add 1 to that DMax result.

WARNING: If you have a high "record creation rate" such that two users could try to create a new record like this at the same time, you need to be prepared for an error to occur regarding duplication. Using the "real" autonumber, that can't happen because Access knows to keep proper track of autonumber fields.

 

[Pat Hartman]

I'd like to change Doc's item #2 slightly. The expression should use DMax() rather than DCount() just in case you need to delete any records and it needs to handle a null value return as it will when the first new row is created each year.

NewUserID = NZ(DMax("YearSeq", "yourtablename", "YearNum" = Year(Date()), 0) + 1

The expression gets the maximum sequence number for the specified year and adds 1 to it. The Nz() part converts null to 0 so that the arithmetic operation won't fail for the first record of a new year.

Year(Date()) will produce the same result as DatePart("yyyy", Now()) but I think it is easier to understand. Use whichever you prefer.

Don't forget that you MUST add a second unique index to the table to restrict the values of YearNum+YearSeq to ensure that the combination is unique. Then as Doc suggested, you MUST trap the duplicate error generated when two people attempt to add a record at the same time. Just because this is a rare occurrence, doesn't mean you should assume it won't happen. You don't want the user to get some gobbledy gook "duplicates" error. YOU have to prevent it by automatically looping and trying again. One way to minimize the potential for problems is to generate the sequence number as the last statement in the form's BeforeUpdate event. Make sure you test the NewRecord property because you only want to generate a new seq number for new records. That generates the sequence number as close to the save as possible. To test your error trap, the easiest solution is to move the DMax() statement to the form's OnDirty event so that the number is generated as soon as the first character is typed into the record. This will allow you to make sure that two users generate the same number and that will let you test your loop code.

 

[June7]

As already noted, risk of duplication by multiple simultaneous users is a consideration. Do not create the sequence ID and let it hang there without committing to table because in the meantime another user can generate the same ID. Options to reduce this occurrence:

1. create the sequence ID when record initiated and immediately commit to table and user continues input of remaining data

2. create the sequence ID at end of data entry when full record is committed to table.

3. calculate group sequence ID when needed - in query by using the DCount() function or in a report use textbox RunningSum property

 

[Falcone203]

This is what I came up with, As I input a new year in the date field, the new seq num starts

Private Sub Form_Current()
Dim strThisYear As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strYearNow = Format(Now, "yy")
strrnums = DMax("RNum2", "TblRuns")
strThisYear = Format(Date, "yy")
 
    DMaxRNum = strrnums
 
    If (IsNull(DMaxRNum)) And strYearNow = strThisYear Then 'New Empty Data Set
 
    RNum2 = Format(strrnums, 0)
    Me!RNum2 = strThisYear & Format(Nz(strrnums, 0) + 1, "000")

    Else
        If IsNull(Me!RNum2) And strYearNow = strThisYear Then 'new year, bases on the Date entered
    
        strrnums = 0
        Me!RNum2 = strThisYear & Format(Nz(strrnums, 0) + 1, "000")
    
        Else

            If IsNull(Me!RNum2) Then

            Me!RNum2 = Format(Nz(strrnums, 0) + 1, "000")

            End If
        
                If IsNull(Me!Date) Then
            
                MsgBox "Must Enter A Run Date"
                strrnums = 0
                Date2 = ""

                End If
        
        End If

    End If

DoCmd.RunCommand acCmdRefreshPage
Date2 = strThisYear & "-" & Right(RNum2, 3)

End Sub