By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,528 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,528 IT Pros & Developers. It's quick & easy.

How to number in sequence...

P: 83
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

Attached Images
File Type: jpg Untitled.jpg (22.0 KB, 217 views)
Jun 11 '15 #1

✓ answered by 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.

Share this Question
Share on Google+
14 Replies


NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 12 '15 #2

P: 83
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
Jun 14 '15 #3

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 14 '15 #4

P: 83
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..
Attached Files
File Type: zip TZ23Rem.zip (63.2 KB, 39 views)
Jun 15 '15 #5

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 15 '15 #6

P: 83
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;


Expand|Select|Wrap|Line Numbers
  1. Public Sub setLineNums()
  2. Dim rst
  3. Dim vPid, vPrevPID, vDate1, vDate2, vStr1, vStr2, vSeq1, vSeq2
  4. Dim iCount As Long
  5.  
  6. iCount = 0
  7. Set rst = CurrentDb.OpenRecordset("qsMyQuery") '<<-- SELECT Table1.pid, Table1.Date1, Table1.String1, Table1.Sequence1, Table1.Date2, Table1.String2, Table1.Sequence2
  8. FROM Table1;
  9.  
  10. With rst
  11.    While Not .EOF
  12.        vPid = .Fields("pid") & ""
  13.        vDate1 = .Fields("Date1") & ""
  14.        vDate2 = .Fields("Date2") & ""
  15.        vStr1 = .Fields("String1") & ""
  16.        vStr2 = .Fields("String2") & ""
  17.        vSeq1 = .Fields("Sequence1") & ""
  18.        vSeq2 = .Fields("Sequence2") & ""
  19.  
  20.       If vPid <> vPrevPID Then
  21.             iCount = 1
  22.        End If
  23.  
  24.        Select Case True
  25.           Case vStr2 = ""
  26.             .Edit
  27.             .Fields("Sequence1") = iCount
  28.             .Update
  29.             'iCount = iCount + 1     '1
  30.  
  31.           Case vStr2 <> ""
  32.             .Edit
  33.             .Fields("Sequence2") = iCount
  34.             .Update
  35.              iCount = iCount + 1
  36.              .Edit
  37.             .Fields("Sequence1") = iCount
  38.             .Update
  39.            ' iCount = iCount + 1     '2
  40.  
  41.           Case vStr2 = vStr1
  42.             .Edit
  43.             .Fields("Sequence1") = 0
  44.             .Update
  45.             .Edit
  46.             .Fields("Sequence2") = iCount
  47.             .Update
  48.             'iCount = iCount + 1     '3
  49.        End Select
  50.  
  51.        iCount = iCount + 1
  52.  
  53.         vPrevPID = vPid
  54.        .MoveNext
  55.    Wend
  56. End With
  57. Set rst = Nothing
  58. End Sub
  59.  
Jun 16 '15 #7

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jun 17 '15 #8

P: 83
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
Jun 17 '15 #9

P: 83
Expand|Select|Wrap|Line Numbers
  1. Aut1  Seq1  Aut2  Seq2
  2. Record 1     Aut11 Seq11 Aut12 Seq12
  3. Record 2     Aut21 Seq21 Aut22 Seq22
Logic ....

Expand|Select|Wrap|Line Numbers
  1. for each Pid 
  2. for Record 1 iCounter = 0
  3. If Aut2 = Is Null then
  4. Seq12 = 0 iCounter = iCounter + Seq12
  5. Seq11 = 1 iCounter = iCounter + Seq11
  6. Else
  7. Seq12 = 1
  8. If Aut12 = Aut11
  9. Seq11 = 0
  10. Else
  11. Seq11 = 2
  12. End If
  13. End If
  14. If Aut12 = Is Not Null then
  15. Seq12 = 1
  16. If Aut12 = Aut11
  17. Seq11 = 0
  18. Else
  19. Seq11 = 2
  20. End If
  21. End If
  22. Record 2
  23. Seq22 = 2 or 3 Depending on the first record
  24. If Aut22 = Aut21 then
  25. Seq21 = 0
  26. else
  27. Seq21 = 3 or 4 Depending on the first record
  28. end if
and so on till the end of the record.
Jun 17 '15 #10

P: 83
Hi NeoPa,

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

Expand|Select|Wrap|Line Numbers
  1. Do Until .EOF
  2.          If iPID <> .Fields("Pid") Then
  3.             iCounter = 1
  4.             If Nz(.Fields("String2"), "") = "" Then
  5.                .Edit
  6.                .Fields("Sequence1") = 1
  7.                .Update
  8.             End If
  9.                If .Fields("String1") = .Fields("String2") Then
  10.                 .Edit
  11.                 .Fields("Sequence1") = 0
  12.                 .Update
  13.                Else
  14.                 .Edit
  15.                 .Fields("Sequence1") = 1
  16.                 .Update
  17.                End If
  18.             iCounter = iCounter + 1
  19.          End If
  20.          If .Fields("String1") <> .Fields("String2") And Nz(.Fields("String2"), "") <> "" Then
  21.             .Edit
  22.             .Fields("Sequence2") = iCounter
  23.             .Update
  24.             iCounter = iCounter + 1
  25.             .Edit
  26.             .Fields("Sequence1") = iCounter
  27.             .Update
  28.             iCounter = iCounter + 1
  29.          End If
  30.          If .Fields("String1") = .Fields("String2") Then
  31.             .Edit
  32.             .Fields("Sequence2") = iCounter
  33.             .Update
  34.             .Edit
  35.             .Fields("Sequence1") = 0
  36.             .Update
  37.             iCounter = iCounter + 1
  38.          End If
  39.  
It is doing what I want it to and I have some gray hairs now.....Pulled a few hairs too.


Raghu
Jun 20 '15 #11

NeoPa
Expert Mod 15k+
P: 31,768
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?
Jun 22 '15 #12

P: 83
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.
Jun 23 '15 #13

NeoPa
Expert Mod 15k+
P: 31,768
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 :-)
Jun 25 '15 #14

P: 83
Thanks NeoPa for all your encouragement....
Jul 12 '15 #15

Post your reply

Sign in to post your reply or Sign up for a free account.