Comparing Rows in Succession in a Recordset using Arrays and Conditionals

[chopin]

I am working in Access 2003 in Windows XP. I am using Visual Basic for Applications, using DAO to write my modules. What I need to do is write a module that will compare each row to see if they are equal to each other or not. Basically, equal numbers in succession indicate a "music chord" which is why I need to do this. For example, if the table is like so:

Notes.........Chord
A.............. .......111
B.............. .......111
C.............. .......111
F.............. .......56
G.............. ......56
A.............. ......56
A.............. ......43
B.............. ......23
...
Then, I would need output like this:

<<A
B
C>>
<<F
G
A>>
A
B

And the module that I wrote which transposes the columns to rows, produces this output:
<<A B C>> <<F G A>> A B

And this is the correct format I am looking for.

The brackets around the note names indicate a music chord. According to the example above, what I need to do is compare the current recordset under "Chord", to the adjacent "Chord" recordset. If equal, put brackets around note A. Then compare the second recordset with the next recordset. If equal, do nothing. Then compare the 3rd recordset with the 4th recordset. Since it is NOT equal, put opposite backets around the note "c". And so on.

What I don't know how to do is fetch a row, analyze it, manipulate it and move to the next row using DAO. For simplicity, here is a piece of code that I would like to get working, in order to crack this: (Code does NOT work, I need to use an array but I do not know how to implement it.)

Do Until rst.EOF
    strChord = rst!Chord
    If strChord = "111" Then
    sSQL = "INSERT INTO Test (Notes)" & "VALUES('ItWorks' & '" & strNote & "')"
    db.Execute sSQL
    Else
    sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
    db.Execute sSQL
    End If
    rst.MoveNext
Loop

The logic in words....If Chord = "111" then insert values "ItWorksX", into table Test under column Notes where X is a Note letter, for any Chord value that equals "111". So the output according to the above data set (if written correctly) would be:

Notes
ItWorksA
ItWorksB
ItWorksC
F
G
A
A
B

I hope this is clear enough. I think I need to use an array "getrows?" but I am not sure how to use it. I looked all over the web for examples, but I think my situation is unique. I will continue to clarify if need be. Thanks in advance!

[Denburt]

Cnt=1
Do Until rst.EOF
    strNote =rs!Note
    strChord = rst!Chord
    If strChord = strChord1 and Cnt=1 Then
    sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' & '" & strNote & "')"
    db.Execute sSQL
    Elseif strChord <> strChord1 and Cnt>1 Then
      sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
    strChord1 =strChord
   Cnt=1
   else
      sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
      db.Execute sSQL
   End If
    rst.MoveNext
Loop

It may need a little touching up but it should produce the desired results as I understand them.

[Denburt]

After I posted my previous I saw that iwas full of holes this one should be a bit closer.I do tend to over think things at times so if any else has a better idea...

Cnt=1
Do Until rst.EOF
    strChord = rst!Chord
 strNote =rs!Note
 if rs.bof then rs.movenext
 strChord1 = rs!Chord
 rs.moveprevious
 end if
    If strChord = strChord1 and Cnt=1 Then
    sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<'" &  strNote & "')"
    db.Execute sSQL
    Cnt=Cnt+1
    Elseif strChord <> strChord1 and Cnt>1 Then
      sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
     strChord1 =strChord
    Cnt=1
   else
      sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
    db.Execute sSQL
    End If
    rst.MoveNext
Loop

[chopin]

Thank you for getting back to me so quickly. I tried running the code, and all it did was output the Note letters. Furthermore, I think we need one more "else if" for the notes between the brackets. My addition is in bold:

If rst.BOF Then
 rst.MoveNext
 strChord1 = rst!Chord
 rst.MovePrevious
 End If
    If strChord = strChord1 And cnt = 1 Then
    sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' " & strNote & " ')"
    db.Execute sSQL
    cnt = cnt + 1
[B]  ElseIf strChord = strChord1 And cnt > 1 Then
      sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ' )"[/B]
  ElseIf strChord <> strChord1 And cnt > 1 Then
      sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & ">>')"
     strChord1 = strChord
    cnt = 1
   Else
      sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ')"
    db.Execute sSQL
    End If
    rst.MoveNext
Loop

I think the code I added takes into account the notes in the chord between the brackets, like the B and the C in this example:

<<A B C D>>

Let me know if I am wrong. I appreciate your help, and I do understand the logic. This code still does not seem to be working.

[Denburt]

OK Cleaned it up tried and tested example below I think you will find this satisfactory. Let me know if you find a combination I missed.

Set db = CurrentDb
Set rst = db.OpenRecordset("Table1")
cnt = 1
If Not rst.EOF Then
rst.MoveFirst
Do Until rst.EOF
 strChord = rst!Chord
  strNote = rst!Notes
 rst.MoveNext
If Not rst.EOF Then
    strChord1 = rst!Chord
Else
    strChord1 = ""
End If
 rst.MovePrevious
    If strChord = strChord1 And cnt = 1 Then
    sSql = "INSERT INTO Test (Notes) VALUES('<<" & strNote & "')"
    db.Execute sSql
    cnt = cnt + 1
    ElseIf strChord <> strChord1 And cnt > 1 Then
      
     sSql = "INSERT INTO Test (Notes) VALUES(""" & strNote & ">>"")"
    db.Execute sSql
    cnt = 1
   Else
      sSql = "INSERT INTO Test (Notes) VALUES('" & strNote & "')"
        Debug.Print sSql
        db.Execute sSql
    End If
    rst.MoveNext
Loop
End If

[chopin]

Well well, thank you very much, problem solved. I am surprised I was able to portray exactly what I needed done, and you solved this...the hardest part of my project.

Anyway, in case you are curious with what I am doing, I am trying to convert musicXML into lilypond format (lilypond is a free music typesetter which requires a special formatting, which then compiles the lilypond code into sheet music that is a PDF). Since I am a composer, converting musicXML into lilypond format would save me a tremendous amount of time rather than to manually do it by hand.

I will bump this thread if I have further questions, as I am working on this project currently, and so far, this was the biggest obstacle.

Again, thank you!

[Denburt]

My pleasure glad I could help :)