Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 http://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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.