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

Comparing Rows in Succession in a Recordset using Arrays and Conditionals

P: 37
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.)

Expand|Select|Wrap|Line Numbers
  1. Do Until rst.EOF
  2.     strChord = rst!Chord
  3.     If strChord = "111" Then
  4.     sSQL = "INSERT INTO Test (Notes)" & "VALUES('ItWorks' & '" & strNote & "')"
  5.     db.Execute sSQL
  6.     Else
  7.     sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
  8.     db.Execute sSQL
  9.     End If
  10.     rst.MoveNext
  11. Loop
  12.  
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!
Mar 21 '07 #1
Share this Question
Share on Google+
6 Replies


Denburt
Expert 100+
P: 1,356
Expand|Select|Wrap|Line Numbers
  1. Cnt=1
  2. Do Until rst.EOF
  3.     strNote =rs!Note
  4.     strChord = rst!Chord
  5.     If strChord = strChord1 and Cnt=1 Then
  6.     sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' & '" & strNote & "')"
  7.     db.Execute sSQL
  8.     Elseif strChord <> strChord1 and Cnt>1 Then
  9.       sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
  10.     strChord1 =strChord
  11.    Cnt=1
  12.    else
  13.       sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
  14.       db.Execute sSQL
  15.    End If
  16.     rst.MoveNext
  17. Loop
  18.  
It may need a little touching up but it should produce the desired results as I understand them.
Mar 21 '07 #2

Denburt
Expert 100+
P: 1,356
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...
Expand|Select|Wrap|Line Numbers
  1. Cnt=1
  2. Do Until rst.EOF
  3.     strChord = rst!Chord
  4.  strNote =rs!Note
  5.  if rs.bof then rs.movenext
  6.  strChord1 = rs!Chord
  7.  rs.moveprevious
  8.  end if
  9.     If strChord = strChord1 and Cnt=1 Then
  10.     sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<'" &  strNote & "')"
  11.     db.Execute sSQL
  12.     Cnt=Cnt+1
  13.     Elseif strChord <> strChord1 and Cnt>1 Then
  14.       sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & ">>')"
  15.      strChord1 =strChord
  16.     Cnt=1
  17.    else
  18.       sSQL = "INSERT INTO Test (Notes)" & "VALUES('" & strNote & "')"
  19.     db.Execute sSQL
  20.     End If
  21.     rst.MoveNext
  22. Loop
  23.  
Mar 21 '07 #3

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

Expand|Select|Wrap|Line Numbers
  1. If rst.BOF Then
  2.  rst.MoveNext
  3.  strChord1 = rst!Chord
  4.  rst.MovePrevious
  5.  End If
  6.     If strChord = strChord1 And cnt = 1 Then
  7.     sSQL = "INSERT INTO Test (Notes)" & "VALUES('<<' " & strNote & " ')"
  8.     db.Execute sSQL
  9.     cnt = cnt + 1
  10.   ElseIf strChord = strChord1 And cnt > 1 Then
  11.       sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ' )"
  12.   ElseIf strChord <> strChord1 And cnt > 1 Then
  13.       sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & ">>')"
  14.      strChord1 = strChord
  15.     cnt = 1
  16.    Else
  17.       sSQL = "INSERT INTO Test (Notes)" & "VALUES(' " & strNote & " ')"
  18.     db.Execute sSQL
  19.     End If
  20.     rst.MoveNext
  21. Loop
  22.  
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.
Mar 21 '07 #4

Denburt
Expert 100+
P: 1,356
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.

Expand|Select|Wrap|Line Numbers
  1. Set db = CurrentDb
  2. Set rst = db.OpenRecordset("Table1")
  3. cnt = 1
  4. If Not rst.EOF Then
  5. rst.MoveFirst
  6. Do Until rst.EOF
  7.  strChord = rst!Chord
  8.   strNote = rst!Notes
  9.  rst.MoveNext
  10. If Not rst.EOF Then
  11.     strChord1 = rst!Chord
  12. Else
  13.     strChord1 = ""
  14. End If
  15.  rst.MovePrevious
  16.     If strChord = strChord1 And cnt = 1 Then
  17.     sSql = "INSERT INTO Test (Notes) VALUES('<<" & strNote & "')"
  18.     db.Execute sSql
  19.     cnt = cnt + 1
  20.     ElseIf strChord <> strChord1 And cnt > 1 Then
  21.  
  22.      sSql = "INSERT INTO Test (Notes) VALUES(""" & strNote & ">>"")"
  23.     db.Execute sSql
  24.     cnt = 1
  25.    Else
  26.       sSql = "INSERT INTO Test (Notes) VALUES('" & strNote & "')"
  27.         Debug.Print sSql
  28.         db.Execute sSql
  29.     End If
  30.     rst.MoveNext
  31. Loop
  32. End If
  33.  
Mar 21 '07 #5

P: 37
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!
Mar 21 '07 #6

Denburt
Expert 100+
P: 1,356
My pleasure glad I could help :)
Mar 21 '07 #7

Post your reply

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