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.)
Expand|Select|Wrap|Line Numbers
- 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
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!