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

 

[My Aswer Is This]

Lets talk about terminology.
When you say you have a Table MM

Do actually mean A Excel Table or do you mean a sheet named MM
A Table is a Range on a sheet.
And if it is a Table we need to say column 1 of the Table named MM

And you said:
Write values of Row 1, 4, and 5 to a New table

Assuming you mean sheet. Where on the sheet do you want them copied?

 

[jex57]

Hi
Thank you for getting back to me.
I mean a table in MS access called MM. MM has a list of values in one column (field) that I need to loop through and when I find a match write that match to a different table called MMatched.

 

[My Aswer Is This]

I'm sorry. I missed the part where your posting is about MS Access.
I thought this was a Excel Question.
I know very little about Access.

 

[FatBoyClam]

Can you illustrate what you expect the output for MMatched looks like?

It sounds suspiciously like you want a sorted output for values which appear more than once.

If so, then this SQL should do it.

SELECT MM.Field1
FROM MM
WHERE (((MM.Field1) In (select Field1 from MM group by Field1 having count(Field1)>1)))
ORDER BY MM.Field1;

 

[jex57]

Hi
thank you for this.
I get the SQL but my issue is
a) writing it to the new table
b) if there is more than 1 match it would then contain a dynamic number of fields.
c) how do I loop through the record set - so start at row 1 - loop through all the remaining rows, then move to row 2 and loop through from row 1 again down?

 

[FatBoyClam]

a) If the set-based approach (SELECT) works, then writing it to a new table is a matter of making it an INSERT query. You can do this in the query desginer in Access - on the Ribbon there's Make Table (if the table doesn't exist) or "Append" (when the table does already exist).
b) the output of the SQL above would be this;
Query1

Field1
test1
test1
test1
test2
test2

Not sure what you mean by a 'dynamic number of fields'. Again - it would help if you were able to illustrate what you expect the output to be.
c) Yes. You need to remember that tables have no inherent order in MS Access. Unless you have at least an index, preferably a Primary Key, the term "row 1" is a bit meaningless. However, you could do what you describe in VBA by loading the data into a recordset object and looping that. I can help with that, but I need to understand what you're trying to get out of it to be able to confirm this is the way to go about it.

 

[welshgasman]

I would create a distinct query to get unique values.
I would then use that as my driving recordset.
For each value, I would run some dynamic sql (where the criteria is the value of the field) as another recordset from the orignal table.
For every record in that recordset, I would add a new record to the outgoing table and update that record.

Alternative create a totals query with a count of each unique value.
Then run an append query a number of times for the number of each value.?

I would struggle to do it via queries only.?

However I am not sure of why you are doing what you are trying to do.?
A lot ot times we are asked how to do something when the asker is only asking as they do not know any other way and often this is the incorect approach.?
That might not be the case here, but the requirements do sound strange.?

HTH

 

[xenou]

Agree that it might be worth more info on the actual goal and perhaps more realistic data.

 

[jex57]

HI

Thank you all for the responses.
Am trying to do it in VBA because the queries will be more complicated.
The goal is to match a single column of data and almost like remove duplicates, but I dont want to remove the duplicates, I want to write them to a new table. The ultimate plan is to be able to use fuzzy logic to match similar terms so I would have a single term TEST1 which would be matched to TEST 1 TEST 1 1TEST. The matching I can do because I found code on the internet. Where my issues comes in is actually looping through a single column (if I had my data into different tables it would be easier).

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.