How to number in sequence...

[raghuprabhu]

Hi All

I have a table in a database with 7 columns. The data is sorted by Date1 in descending order.

For each pid I want to put the sequence numbers
First record has two conditions
If string 2 is null then start numbering from sequence1
If string 2 is not null then start numbering from sequence2
If string 2 = string1 then
Sequence1 = 0
Second record has two conditions
Number sequence2 with the value 2 or 3 depending on the line one
If string 2 = string1 then
Sequence1 = 0
Else
Sequence1 = next number
Same condition for the rest of records

Thanks

Raghu

 

[Ranman256]

try this, but fill in yours for qsMyQuery...

Public Sub setLineNums()
Dim rst
Dim vPid, vPrevPID, vDate1, vDate2, vStr1, vStr2, vSeq1, vSeq2
Dim iCount As Long

iCount = 0
Set rst = CurrentDb.OpenRecordset("qsMyQuery")
With rst
   While Not .EOF
       vPid = .Fields("pid") & ""
       vDate1 = .Fields("Date1") & ""
       vDate2 = .Fields("Date2") & ""
       vStr1 = .Fields("String1") & ""
       vStr2 = .Fields("String2") & ""
       vSeq1 = .Fields("Sequence1") & ""
       vSeq2 = .Fields("Sequence2") & ""
       
      If vPid <> vPrevPID Then
            iCount = 1
       End If

       Select Case True
          Case vStr2 = ""
            .Fields("Sequence1") = iCount
          
          Case vStr2 <> ""
            .Fields("Sequence2") = iCount
             iCount = iCount + 1
            .Fields("Sequence1") = iCount
          
          Case vStr2 = vStr1
            .Fields("Sequence1") = 0
            .Fields("Sequence2") = iCount
       End Select
       
       iCount = iCount + 1
       
        vPrevPID = vPid
       .movenext
   Wend
End With
Set rst = Nothing
End Sub

 

[vbaInet]

Raghu, there are some problems with your logic.

* Date2 - Null, Seq2 - 0: fails your first condition, "string 2 is null then start numbering from sequence1".

* Date2 - 21 May, Seq2 - 2: fails one of your conditions. It should be 1 and not 2 because in the previous row Seq1 is 0, so it should be a continuation.

I think if you can sort out your logic then it can be done in a query.

 

[MarkK]

How does this sequencing add value to your data? It essentially breaks normalization rules by duplicating a sort order which could be achieved more simply by sorting and filtering on the the original criteria. So which sort order is taken as correct, and if you edit the data on which this sort order is based, do you need to update it?

It seems like a liability to me to add these fields, and implement this secondary sequencing hurdle.

 

[raghuprabhu]

Thanks a lot to everyone who has commented and answered. Basically these are certain transactions.

The object of this exercise is I have to create a string to programmatically delete a huge number of transactions in our pay system. There are 6,000 employees (pid) and 300,000 transactions to delete before the end of this financial year ie 30JUN15. Some employees have 3 or 4 transactions to be deleted while some others have up to 75 transactions. To delete these transactions I have to create a deleting script. The transactions have to be deleted in the order last in, first to be deleted.

Where sequence2 and date2 are blank indicates the transaction is open ended.

Where sequence1 and sequence2 same the transaction was close ended.

Where the sequence1 and sequence2 are not the same, a closing transaction was input sometime after the opening transaction was input.

Ranman256, attached a small database with the samples in the tif I posted before.

Your code is working except when String2 = String2

Raghu Prabhu

 

[vbaInet]

I'm still not following your logic and I don't understand what the terms "open ended" and "closed ended" mean in this context.

In my last post I highlighted two possible flaws in your logic but these haven't been cleared up.

Your code is working except when String2 = String2

If you're happy with Ranman256's code, then it should be a simply matter for you to add in the extra logical test.