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.

 

[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.

 

[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.

 

[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