Data analysis in the Table

P

pshem

New member
Local time
Today, 01:42
Joined
Oct 28, 2018
Messages
6
I have a table in which are: ID_ILE and ILE. In ILE there are numbers (successive records) in turn 1. 2. 3, -1, -2, -, 3, -4, 5, 6, 7, 1, 2, -10, -11, -12, -13 -, 14 etc. And now I need a function that in the form of a table will give me the result 3, -4, 5, -5. These numbers illustrate how many successively positive and negative negative ILE are.
Can you help?
 
This is not well-suited for SQL since it involves successive comparisons. This is the sort of thing that will probably work best using programming in VBA. Before we go there, the question is, how familiar are you with VBA programming?

I only ask because this appears to be your first post. Therefore, we don't know from your forum history just how comfortable you are with programming and your profile is mostly empty so doesn't tell us much either.
 
This works for me
Code: 
Public Function GetLongestSequence() As Collection
  Const tableName = "tblILE"
  Const fieldName = "ILE"
  Const OrderByName = "ID_ILE"
  Dim newColl As New Collection
  Dim longestColl As New Collection
  Dim PreviousValue As Long
  Dim CurrentValue As Long

  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim CurrentString As String
  Dim NewString As String
  strSql = "Select " & fieldName & " FROM " & tableName & " ORDER BY " & OrderByName
 ' Debug.Print strSql
  
  
  Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  CurrentValue = RS.Fields(fieldName)
  newColl.Add CurrentValue
  PreviousValue = CurrentValue
  RS.MoveNext
  Do While Not RS.EOF
    CurrentValue = RS.Fields(fieldName)
    If (CurrentValue < 0 And PreviousValue > 0) Or (CurrentValue > 0 And PreviousValue < 0) Then
      newColl.Add CurrentValue
      If longestColl.Count < newColl.Count Then Set longestColl = newColl
      PreviousValue = CurrentValue
    Else
      Set newColl = New Collection
      newColl.Add CurrentValue
      PreviousValue = CurrentValue
    End If
    RS.MoveNext
  Loop
  Set GetLongestSequence = longestColl
End Function

Public Sub InsertSequence()
  Dim Sequence As Collection
  Dim strSequence As String
  Dim I As Integer
  Dim TheItem As Long
  Set Sequence = GetLongestSequence
  
  For I = 1 To Sequence.Count
    TheItem = Sequence(I)
    'Do insert query to put the value where you want it
    strSequence = strSequence & " " & TheItem
    
  Next I
  'for demo
  Debug.Print strSequence
End Sub

Just need specifics on what to do with the sequence after you get it.
 
As a result of Sequence must be created, a table Res_ile in which there are columns: Id and ILE:

Id ILE
1 3
2 -4
3 5
4 -5

To The_Doc_Man:
Unfortunately, I know only the basics of VB for now.
 
My solution provides the correct sequence.
 
Maybe was not clear. I did not do the needed insert query to save your results because you did not provide the specifics. But that code should return the correct sequence and print it out in the debug window for you to verify. If you run InsertSequence(), look in the immediate window and see if it prints out. If not what are the errors? Can you provide more details on where and how you want to store the results.
 
Unfortunately, I can not use the Insert query. :-(
The results are to be stored in the Res_ile table. This table has columns Id and Ile.
 
Are your Source table and destination table different tables
I have a table in which are: ID_ILE and ILE.
Click to expand...
The results are to be stored in the Res_ile table. This table has columns Id and Ile.
Click to expand...

If they are the same table you could have a second field with "In_Sequence" field then just update that field to identify if it is in sequence.
 
I Interpret this to mean you have a source table (which I do not know the name of). In my example I named it "tblILE". It looks like

Code: 
ID_ILE	ILE
1	1
2	2
3	3
4	-1
5	-2
6	3
7	-4
8	5
9	6
10	7
11	1
12	2
13	-10
14	-11
15	-12
16	-13
17	-14

You have a destination table called. Res_ILE. It looks like.

ID ILE

If I run the code InsertSequence I get the following in the destination table.

Code: 
ID	ILE
5	-2
6	3
7	-4
8	5

Updated Code

Code: 
Public Function GetLongestSequence() As Collection
  'Change Names as needed
  Const tableName = "tblILE"
  Const ILE_fieldName = "ILE"
  Const OrderByName = "ID_ILE"
  Const ID_FieldName = "ID_ILE"
  
  'Save the IDs not the values
  Dim newColl As New Collection
  Dim longestColl As New Collection
  Dim PreviousValue As Long
  Dim CurrentValue As Long
  Dim CurrentID As Long
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim CurrentString As String
  Dim NewString As String
  strSql = "Select * FROM " & tableName & " ORDER BY " & OrderByName
 ' Debug.Print strSql
  
  
  Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  CurrentValue = RS.Fields(ILE_fieldName)
  CurrentID = RS.Fields(ID_FieldName)
  newColl.Add CurrentValue
  PreviousValue = CurrentValue
  RS.MoveNext
  Do While Not RS.EOF
    CurrentValue = RS.Fields(ILE_fieldName)
    CurrentID = RS.Fields(ID_FieldName)
    If (CurrentValue < 0 And PreviousValue > 0) Or (CurrentValue > 0 And PreviousValue < 0) Then
      newColl.Add CurrentID
      If longestColl.Count < newColl.Count Then Set longestColl = newColl
      PreviousValue = CurrentValue
    Else
      Set newColl = New Collection
      newColl.Add CurrentID
      PreviousValue = CurrentValue
    End If
    RS.MoveNext
  Loop
  Set GetLongestSequence = longestColl
End Function

Public Sub InsertSequence()
  Dim Sequence As Collection
  Dim strSequence As String
  Dim I As Integer
  Dim TheItem As Long
  Dim strSql As String
  'Change names as needed
  Const SourceTableName = "tblILE"
  Set Sequence = GetLongestSequence
  
  For I = 1 To Sequence.Count
    TheItem = Sequence(I)
    'Do insert query to put the value where you want it
    If strSequence = "" Then
     strSequence = TheItem
    Else
      strSequence = strSequence & ", " & TheItem
    End If
  Next I
  strSequence = "(" & strSequence & ")"
  strSql = "Insert into res_ILE (ID, ILE) Select ID_ILE, ILE FROM " & SourceTableName & " WHERE ID_ILE in " & strSequence
  CurrentDb.Execute strSql
End Sub

This will only work if all the Names are correct. Not sure what the purpose is, but I think if I needed to identify the longest sequence in a table, I would add the "In_Sequence" field to the source table to identify the sequence instead of pushing the results to a new table.
 
So if you had a table like below the following would identify those in the sequence. Then rerun the code when you update the table with new values. From there you can query where "In_Sequence = TRUE" and export to wherever you want.

TblILE
Code: 
ID_ILE	ILE	In_Sequence
1	1	No
2	2	No
3	3	No
4	-1	No
5	-2	Yes
6	3	Yes
7	-4	Yes
8	5	Yes
9	6	No
10	7	No
11	1	No
12	2	No
13	-10	No
14	-11	No
15	-12	No
16	-13	No
17	-14	No

Code: 
Public Sub UpdateSequence()
  'Change Names as needed
  Const tableName = "tblILE"
  Const ILE_fieldName = "ILE"
  Const OrderByName = "ID_ILE"
  Const ID_FieldName = "ID_ILE"
  
  'Save the IDs not the values
  Dim newColl As New Collection
  Dim longestColl As New Collection
  Dim PreviousValue As Long
  Dim CurrentValue As Long
  Dim CurrentID As Long
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim I As Integer
  
  strSql = "Select * FROM " & tableName & " ORDER BY " & OrderByName
 ' Debug.Print strSql
  
  
  Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  CurrentValue = RS.Fields(ILE_fieldName)
  CurrentID = RS.Fields(ID_FieldName)
  newColl.Add CurrentValue
  PreviousValue = CurrentValue
  RS.MoveNext
  Do While Not RS.EOF
    CurrentValue = RS.Fields(ILE_fieldName)
    CurrentID = RS.Fields(ID_FieldName)
    If (CurrentValue < 0 And PreviousValue > 0) Or (CurrentValue > 0 And PreviousValue < 0) Then
      newColl.Add CurrentID
      If longestColl.Count < newColl.Count Then Set longestColl = newColl
      PreviousValue = CurrentValue
    Else
      Set newColl = New Collection
      newColl.Add CurrentID
      PreviousValue = CurrentValue
    End If
    RS.MoveNext
  Loop
  'Clear out old sequence values
  strSql = "Update " & tableName & " SET In_Sequence = FALSE"
  CurrentDb.Execute strSql
  
  'update the In_Sequence field
  For I = 1 To longestColl.Count
    RS.FindFirst ID_FieldName & " = " & longestColl(I)
    RS.Edit
      RS.Fields("In_Sequence") = True
    RS.Update
  Next I
End Sub
 
Thank you very very very much for your time on my problem

You're right. I have a source table named "tblILE". It looks like.

ID_ILE ILE
1 1
2 2
3 3
4 -1
5 -2
6 3
7 -4
8 5
9 6
10 7
11 1
12 2
13 -10
14 -11
15 -12
16 -13
17 -14

And I have a destination table called. Res_ILE.

After running the InsertSequence code, I should receive the following data in the target table.

ID
(AutoNumber) ILE
1 3 because there are 3 positive numbers in sequence (1,2,3)
2 -2 because then there are 2 negative numbers in sequence (-1,-2)
3 1 because then there is 1 positive numbers in sequence (3)
4 -1 because then there is 1 negative numbers in sequence (-4)
5 5 because there are 5 positive numbers in sequence (5,6,7,1,2)
6 -5 because there are 5 negative numbers in sequence (-10,-11,-12,-13,-14)
 
OH, what a weird coincidence. I interpreted this to mean
will give me the result 3, -4, 5, -5. These numbers illustrate how many successively positive and negative negative ILE are
Click to expand...

The longest sequence of numbers flipping back from negative to positive. Which I thought was in your table which does have 3,-4, 5. this is actually an easier problem know that I see the results. Give me a second to update.
 
Code: 
Public Sub UpdateSequence2()
  'Change Names as needed
  Const SourceTableName = "tblILE"
  Const ILE_fieldName = "ILE"
  Const OrderByName = "ID_ILE"
  Const ID_FieldName = "ID_ILE"
  Const DestinationTableName = "Res_ILE"
  'Save the IDs not the values
  Dim PreviousSign As Long
  Dim CurrentSign As Long
  Dim Counter As Long
  
  Dim RS As DAO.Recordset
  Dim strSql As String
  Dim I As Integer
  
  strSql = "Select * FROM " & SourceTableName & " ORDER BY " & OrderByName
 ' Debug.Print strSql
  
  
  Set RS = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  CurrentSign = RS.Fields(ILE_fieldName) / Abs(RS.Fields(ILE_fieldName))
  PreviousSign = CurrentSign
  Counter = 1
  RS.MoveNext
  Do While Not RS.EOF
    CurrentSign = RS.Fields(ILE_fieldName) / Abs(RS.Fields(ILE_fieldName))
    If CurrentSign = PreviousSign Then
      Counter = Counter + 1
      PreviousSign = CurrentSign
    ElseIf CurrentSign <> PreviousSign Then
      'Change taken place
      strSql = "Insert into " & DestinationTableName & " (ILE) Values(" & Counter * PreviousSign & ")"
      'Debug.Print strSql
      CurrentDb.Execute strSql
      PreviousSign = CurrentSign
      Counter = 1
    End If
    If RS.AbsolutePosition = RS.RecordCount - 1 Then
      'No change in the last round
      strSql = "Insert into " & DestinationTableName & " (ILE) Values(" & Counter * PreviousSign & ")"
      'Debug.Print strSql
      CurrentDb.Execute strSql
    End If
    RS.MoveNext
  Loop
End Sub

This gives me this in the Res_ILE

Code: 
ID	ILE
1	3
2	-2
3	1
4	-1
5	5
6	-5

Do you want to add to the Res_ILE table another fields. You could store the actual sequence too?


ID ILE Sequence
1 3 (1,2,3)
 
Perfect perfect perfect!!!!
You have very much knowledge.

Thank you very much for your help.
You are great!!!
 
You must log in or register to reply here.

Similar threads

A
Counting specific numbers in textbox of two or more rows , (no duplicates)
Replies
12
Views
493
CJ_London
C
C
Query Help on "Lookup" Type Function
Replies
12
Views
266
arnelgp
arnelgp
A
Tab Index of fields NOT reacting in Order
Replies
5
Views
209
The_Doc_Man
The_Doc_Man
H
Query combining records
Replies
13
Views
571
theDBguy
theDBguy
C
When accHitTest generates an 'invalid procedure call or argument' error
Replies
4
Views
185
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom