How to number in sequence...

[hrprabhu]

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

[NeoPa]

There is only a sequence when shown after sorting. Essentially it cannot know the sequence until a point after the data has been produced (This makes sense as database theory works on set theory and ordering and sequencing are no part of that).

Reports can give you sequence numbers as they are objects that display the ordered data. As I say, queries and tables should be considered as a collection of items. Not as a sequence of items.

[hrprabhu]

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

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

Where sequence1 and sequence2 same the transaction was close ended and the input after the event.

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

The object of this exercise is I have to create a string to programmatically delete a huge number of transactions.

I have 6,000 pids and 300,000 transactions to delete before the end of this financial year ie 30JUN15..

Raghu Prabhu

[NeoPa]

This appears to be a question of an entirely different sort. Unfortunately, while you give much more information now, there is still much that isn't clear.

• I would assume that the PID identifies groups of transactions that must be considered together as a set.
• None of the values in your example have a [Sequence2] that is blank. Should we assume none is open ended, or did you mean to say that it would be open endeed if both [Date2] & [String2] are blank?
• What do all the fields actually represent?
• When we actually know what it is we're dealing with, what are the rules that determine that a transaction should be deleted?

Please give clear answers to all these questions so that we can proceed.

[hrprabhu]

Thanks NeoPa,

Will post a sample database.

You are right. I put a conditional format to show if String2 = string1 then string1 is red in colour. I don't want it to be numbered.

other wise sub setLineNums is working fine....

Thanks..

[NeoPa]

Raghu:
Will post a sample database.

You're welcome to post it, but bear in mind that is not what you've been asked to do.

Like many others here, and generally on the web, I won't download something I haven't requested.

I'm looking to help you to understand when you explain your problems. That means you have to explain what they are clearly and not expect me/us to work it out from a database. I don't have that amount of time free for each and every member who posts asking for help. Explaining your own problem isn't too much to expect. Preferably in the first post when you can, but we understand that's difficult for people so sometimes after some help. One way or another though, it must be done by whoever is asking the question.

[hrprabhu]

Basically there are 2 types of transactions
1. Open ended which means Date1and String1 is not blank but Date2 and String2 are.

2. Closed ended which means Date1, String1, Date2 and String2 are not blank. Date1 and Date2 could be same or different, but is String1 = String2

If the transactions are not put at the same time then Date1 and Date2 could be the same but String1 and String2 are not the same.

They can not be deleted randomly, but in the reverse order of them being input. For Pid = 1
The latest transaction dated 22-May-15 is open ended and has to be deleted first. (record1)
Then transaction dated 21-May-15 is open ended and has to be deleted second (record2)
Then transaction dated 14-Apr-15 is open ended and has to be deleted third (record2)
Then transaction dated 12-Mar-15 is closed ended has to be deleted fourth (record3)


pid Date1 String1 Sequence1 Date2 String2 Sequence2
1 22-May-15 ABCD1 1 0 (record1)
1 14-Apr-15 BCDE1 3 21-May-15 BCDE2 2 (record2)
1 12-Mar-15 CDEF1 0 13-Apr-15 CDEF1 4 (record3)

Once the numbers are input I am going to do an union query and get them as follows

Pid date string sequence

1 22-May-15 ABCD1 1
1 21-May-15 BCDE2 2
1 14-Apr-15 BCDE1 3
1 13-Apr-15 CDEF1 4

I can create a query to show all 6000 pid and 300,000 records and send it to a txt file and the programmers can delete the transactions instead of 1000 people deleting the transactions manually. And if it has to be done manually it would take for ever.

I am using the following code to input the sequence numbers, but, it is falling over if string1 = string2

The code for the query "qsMyQuery" is SELECT Table1.pid, Table1.Date1, Table1.String1, Table1.Sequence1, Table1.Date2, Table1.String2, Table1.Sequence2
FROM Table1;

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") '<<-- SELECT Table1.pid, Table1.Date1, Table1.String1, Table1.Sequence1, Table1.Date2, Table1.String2, Table1.Sequence2
FROM Table1;
 
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 = ""
            .Edit
            .Fields("Sequence1") = iCount
            .Update
            'iCount = iCount + 1     '1
 
          Case vStr2 <> ""
            .Edit
            .Fields("Sequence2") = iCount
            .Update
             iCount = iCount + 1
             .Edit
            .Fields("Sequence1") = iCount
            .Update
           ' iCount = iCount + 1     '2
 
          Case vStr2 = vStr1
            .Edit
            .Fields("Sequence1") = 0
            .Update
            .Edit
            .Fields("Sequence2") = iCount
            .Update
            'iCount = iCount + 1     '3
       End Select
 
       iCount = iCount + 1
 
        vPrevPID = vPid
       .MoveNext
   Wend
End With
Set rst = Nothing
End Sub
 

[NeoPa]

This looks like a thorough response. I will need to look into this another day when I'm less tired (and more sober).

The work you've done certainly encourages me to put in more effort trying to understand what is clearly a fairly complex situation. I'll do what I can when I can.

[hrprabhu]

Thanks NeoPa, it is almost working except line 41 to 48 in the code. If string1 = string2 in the record, I don't want to update the number because it is close ended and can be deleted in one go.

Our pay system is antiquated and is nearly 30 years old and we are still stuck with it. I work for the federal government and things are not going to change for a long time into future.

So at your convenience please look at the code.

One of the other things I tried, is update Sequence1 = 0, if String1=String2 then update sequence1 with the next number in sequence only if the field is null. But that is not working either.

Thanks for your help

[hrprabhu]

Aut1  Seq1  Aut2  Seq2
Record 1     Aut11 Seq11 Aut12 Seq12
Record 2     Aut21 Seq21 Aut22 Seq22

Logic ....

for each Pid 
for Record 1 iCounter = 0
If Aut2 = Is Null then
Seq12 = 0 iCounter = iCounter + Seq12
Seq11 = 1 iCounter = iCounter + Seq11
Else
Seq12 = 1
If Aut12 = Aut11
Seq11 = 0
Else
Seq11 = 2
End If
End If
If Aut12 = Is Not Null then
Seq12 = 1
If Aut12 = Aut11
Seq11 = 0
Else
Seq11 = 2
End If
End If
Record 2
Seq22 = 2 or 3 Depending on the first record
If Aut22 = Aut21 then
Seq21 = 0
else
Seq21 = 3 or 4 Depending on the first record
end if

and so on till the end of the record.

[hrprabhu]

Hi NeoPa,

Finally I have been able to tweek it and the following is the part of the code.

Do Until .EOF
         If iPID <> .Fields("Pid") Then
            iCounter = 1
            If Nz(.Fields("String2"), "") = "" Then
               .Edit
               .Fields("Sequence1") = 1
               .Update
            End If
               If .Fields("String1") = .Fields("String2") Then
                .Edit
                .Fields("Sequence1") = 0
                .Update
               Else
                .Edit
                .Fields("Sequence1") = 1
                .Update
               End If
            iCounter = iCounter + 1
         End If
         If .Fields("String1") <> .Fields("String2") And Nz(.Fields("String2"), "") <> "" Then
            .Edit
            .Fields("Sequence2") = iCounter
            .Update
            iCounter = iCounter + 1
            .Edit
            .Fields("Sequence1") = iCounter
            .Update
            iCounter = iCounter + 1
         End If
         If .Fields("String1") = .Fields("String2") Then
            .Edit
            .Fields("Sequence2") = iCounter
            .Update
            .Edit
            .Fields("Sequence1") = 0
            .Update
            iCounter = iCounter + 1
         End If
 

It is doing what I want it to and I have some gray hairs now.....Pulled a few hairs too.


Raghu

[NeoPa]

Raghu,

I'm sorry I haven't had the time to look at this in detail for you yet (Looking through something in detail does take a lot more time I find). Are you telling me that you've managed to work it out by yourself and you're happy with what you have?

[hrprabhu]

Hi NeoPa,

I have been able to figure out and able to get my database to do what I want it to. It is infact doing better than I hoped for.

Thanks for all your encouragement.

[NeoPa]

Any little encouragement I gave was clearly deserved. It was ultimately the time and work that you put in that resulted in you finding your solution. Congratulations :-)

[hrprabhu]

Thanks NeoPa for all your encouragement....