Loop through same column in table

[jex57]

Hi I have a table MM
I need to be able to read the first value in Column A, and match it to the second value in Column A.
For example would have something like this as values in column A

test1
test2
test3
test1
test1
test2

Find the match in row 1 4, and 5
Find the match in row 2 and 6

Write values of Row 1, 4, and 5 to a New table
Write values of row 2, and 6 to same new table


I have no idea where to even start.
Can anyone offer any guidance.

Thank you

 

[welshgasman]

What I need to do is read the value of Row 1 ColumnA - compare it to all the remaining rows of column A and where there is a match write that to another table.
Then read Row 2 Column A - Compare that to and so forth.
I am looking for the loop to read column A, compare to rows in column A, and write to table B - Column . Some of column A will be matched to 10 rows and others might only be matched to 4 - so almost create dynamic columns in the new table as well. If there are only 7 columns and I have 8 matches - create new column.

Hope this makes sense.

Repeating what you think you need to do is not going to make it any clearer.?

If you want to fuzzy compare against values in Field1 why can you not just compare them where they are.?
At present I cannot see any benefit of copying these to another table.? Are you going to repeat this process regularly.?

 

[jex57]

Repeating what you think you need to do is not going to make it any clearer.?

If you want to fuzzy compare against values in Field1 why can you not just compare them where they are.?
At present I cannot see any benefit of copying these to another table.? Are you going to repeat this process regularly.?

Process has to be repeated regularly. The reason that I want to copy it to another table is that currently I have no view of similar terms (rows going downwards will not give me that view when working with over 3000 values even if sorted). If I can put them in a new table I am building a final table / library of similar terms and can see a primary term and all its synonyms that have been approved.

I got as far as writing the initial loop:

Private Sub Command0_Click()
Dim sql As String
Dim data1, data2 As String ''Undefined
Dim db As Database
Dim rst As Recordset
Dim RecordCount As Integer

sql = "Select COLUMN_NAME from guest_MMT_C;"
RecordCount = 0

Set db = CurrentDb
Set rst = db.OpenRecordset(sql)
Do Until rst.EOF
RecordCount = RecordCount + 1

data1 = rst("COLUMN_NAME")
data2 = rst("COLUMN_NAME")



rst.MoveNext
Loop
End Sub

My problem is then I dont know how to compare the values in the same column. (new to coding, so know how to compare from two different sql strings but not from the same column)

 

[welshgasman]

Right.
Firstly you have to Dim every variable's type?

Dim data1, data2 As String

makes data1 a variant, and ONLY data2 a String. Start giving some decent names to your variables that indicate not only what they are for but also the type. Believe me, otherwise it will start getting confusing when you write more than a few lines, or come back to the code 3 montsh later.

The standard I believe (or at least what I use) is RVBA Naming Conventions - Xoc Software (RVBA Conventions, Maya Calendar, et.al.)

Read my post in post #8 again.
In English
1. you want to get all unique values.
2. Then for each value you want to get all the records with that value
3. For those records found, you now add a new record to your outgoing table.

So first recordset might be rstUnique, second rstFind and thirs rstOutput (or the table name)

Take each step at a time. So get your outer loop/recordset correct, then move on to the inner loop, then the output.
Either put plenty of debug.print statements in to see what is actually (not what you *think* is) happening, or step through the code line by line and onspect the variableswhen the code doe snot behave as you were expecting it to.

You are working with three recordsets here, and a loop within a loop, so not the eaisiest task to start off learning VBA with.

That is of course if you take my suggestion.

HTH

 

[jex57]

Right.
Firstly you have to Dim every variable's type?

Dim data1, data2 As String

makes data1 a variant, and ONLY data2 a String. Start giving some decent names to your variables that indicate not only what they are for but also the type. Believe me, otherwise it will start getting confusing when you write more than a few lines, or come back to the code 3 montsh later.

The standard I believe (or at least what I use) is RVBA Naming Conventions - Xoc Software (RVBA Conventions, Maya Calendar, et.al.)

Read my post in post #8 again.
In English
1. you want to get all unique values.
2. Then for each value you want to get all the records with that value
3. For those records found, you now add a new record to your outgoing table.

So first recordset might be rstUnique, second rstFind and thirs rstOutput (or the table name)

Take each step at a time. So get your outer loop/recordset correct, then move on to the inner loop, then the output.
Either put plenty of debug.print statements in to see what is actually (not what you *think* is) happening, or step through the code line by line and onspect the variableswhen the code doe snot behave as you were expecting it to.

You are working with three recordsets here, and a loop within a loop, so not the eaisiest task to start off learning VBA with.

That is of course if you take my suggestion.

HTH

Thank you - will heed your advise on the naming conventions - but in english:
Read first record (rstUnique)
Read second Record (rstFind)
If rstUnique = rstFind (1) then
Create rstOutput
move next record in rstfind
compare rstUnique to rstFind (2)....


What I am not getting is how to build this loop in VBA

How do I reference the first record in rstUnique and how do I reference the first, then the second record etc in rstFind?


Hope this is making some sense.
Thanks for your help

 

[welshgasman]

No, you just read a record in rstUnique (OK, the very first will be first),but do not fous on that as you will need to read every unique record.
Then your SQL will retrieve all records equal that record's value so you will need a WHERE clause, then you process all those records to your output file.
Then you read the next rstUnique record, and repeat until you get to EOF of rstUnique.

 

[xenou]

What I need to do is read the value of Row 1 ColumnA - compare it to all the remaining rows of column A and where there is a match write that to another table.

do you mean an exact match or are you talking about matching with your fuzzy logic?

and when you say write to another table, do you mean just the single value you "matched" or the entire row where you found the match?

Note that so far it is not at all clear why a loop would be necessary here. In general I would NOT use a loop in vba (or vba at all) unless it was necessary.

 

[FatBoyClam]

@jex57 - you're still being really vague as to what you want to achieve, but I think the key is that you want to Pivot the data, a bit like the Excel request on this thread.

So, the new table will have one row for each unique value (let's ignore whether the matches are fuzzy or not) and all the matches returned in columns, thus the shape of the table is dynamic.

So - the example data you provided would give this result;

Table1

OriginalField	Match001	Match002
test1	test1	test1
test2	test2

With fuzzy matching it might be something like this

Table1

OriginalField	Match001	Match002
test1	Test 1	1test
test2	tesT2

This feels like one of those problems where the tools you're using are not suited to what you're trying to do, but it should be doable, in principal.

First I'd suggest you run a query to get the number of uniques and the maximum number of matches per unique, then you need to create a table structured with matches+1 columns. I'd add an extra column for the number of matches filled in, this would be 0 at the outset.

Then I'd loop through the original recordset, check the first column of your new table for the current value, add a new row with that value if it doesn't exist, otherwise fill the first blank match column with the new value if it's already got a row, and then increment the count column by one (this just makes it easier to know which match column to fill on each pass).

Once you've completed your loop, you can drop the count column, or you could just ignore it when subsequently querying the table for whatever you intend to use it for.

It still feels like you're trying to get to a result all the wrong way.