Ms-access – a proper way to handle a “sequence number” like field

ms access

The fields of a trimmed-down version of a database I'm working with looks like this:

Model Number, Model Name, Customer, Date, SeqNum

The customer can purchase the exact same item on the same date so we're going to use a sequence number to distinguish the rows. So entries might look like:

AS-23423, Box, John Doe, 01/22/2013, 00
AS-23423, Box, John Doe, 01/22/2013, 01
AS-23423, Box, John Doe, 01/22/2013, 02
AS-23445, Pen, John Doe, 01/22/2013, 00
AS-23454, Case, John Doe, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 00
AS-23423, Box, Mary Lyn, 01/22/2013, 01

How should you handle the sequence number if an application has to be CRUD compliant? I've considered doing MAX(SeqNum) + 1 upon inserting but this seems off especially when handling update and delete, so I figured there might be a better way.

Best Answer

In Access, this is more about code than design, unless you decide instead on an autonumber, which provides the required unique field and is much easier and safer. Otherwise, in https://stackoverflow.com/questions/11949603/access-vba-find-max-number-in-column-and-add-1/11950647#11950647, you will find the following notes:

This requires a reference to the Microsoft ActiveX Data Objects x.x Library

Sequential Numbers

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String

    lngResult = 0  'assume fail

    strCon = "Provider=" ''Connection to back-end
    cn.Open strCon

    rs.CursorType = adOpenKeyset
    rs.LockType = adLockPessimistic
    rs.CursorLocation = adUseServer

    ''Where BEInfo is a single line table
    strSQL = "SELECT ASeqNumber FROM BEInfo"

    rs.Open strSQL, cn, , , adCmdText

    'Note this is ADO, so no rs.Edit
    rs!ASeqNumber = rs!ASeqNumber + 1
    rs.Update

    lngResult = rs!ASeqNumber

    ''This should not happen, but just to be sure
    If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
        lngResult = 0
    End If
Related Question