New to VBA in MS access. Can anyone help in writing some code on this to help me, so i can learn to understand how the below logic is done in the vba code? Much appreciated in advance.
I have 2 tables (TABLE A and TABLE B) in MS Access.
In table 'A' there is a list of string text letters in column 'string_text_names'.
The list 'string_text_names' in table 'A' may contain more than 100+ records.
For example:
string_text_names
-----------------
euston
aldgate
leicester
temple
bank
highbury+islington
ealingBwdy
grandstation
In table B there is a table containing numerous columns of data. In particular, we shall focus on columns 'Long_Name' and column 'Marker', delimited by PIPE in the below table 'B'.
Long_Name | Marker
-----------------------------
euston-32434 |
euston1 |
euston 1234 |
cat |
eaton |
bank245rfwef |
temple |
temple-1 |
temple-2 |
temple-234 |
baker |
robin |
leicester |
Question: I can't think of a efficient way to place the 'Yes' text in the 'Marker' column in table 'B', where for each record in table 'A' if the string text matches the prefix string text found in the 'Long_Name' string in table 'B'.
For example:
1.Get text 'euston' in column 'string_text_names' from table 'A'.
2.Check in table 'B', where for each record if the 'Long_Name' begins with 'euston' in the string text,
3. If yes, then add 'YES' text to the corresponding 'Marker' column in table B. If no, then move to the next record in the 'Long_Name' in table 'B'
4 Go through all the records in table 'B' on the 'Long_name' column and adding the 'YES' text in the 'Marker' column where the prefix texts both matches.
5.Repeat the process 1 to 4 again but with the next 'string_text_names' in table 'A' 'aldgate'.
6. Until all the 'string_text_names' records have been examined against the 'Long_Name' text in table 'B', then finally the process can end.
The FINAL table would look something like this.
Long_Name | Marker
-----------------------------
euston-32434 | YES
euston1 | YES
euston 1234 | YES
cat |
eaton |
bank245rfwef | YES
temple | YES
temple-1 | YES
temple-2 | YES
temple-234 | YES
baker |
robin |
leicester | YES
I think this needs to be done in VBA and not by MS Access macros, as one can't
determine how many 'string_text_names' there will be in the list.
I have 2 tables (TABLE A and TABLE B) in MS Access.
In table 'A' there is a list of string text letters in column 'string_text_names'.
The list 'string_text_names' in table 'A' may contain more than 100+ records.
For example:
string_text_names
-----------------
euston
aldgate
leicester
temple
bank
highbury+islington
ealingBwdy
grandstation
In table B there is a table containing numerous columns of data. In particular, we shall focus on columns 'Long_Name' and column 'Marker', delimited by PIPE in the below table 'B'.
Long_Name | Marker
-----------------------------
euston-32434 |
euston1 |
euston 1234 |
cat |
eaton |
bank245rfwef |
temple |
temple-1 |
temple-2 |
temple-234 |
baker |
robin |
leicester |
Question: I can't think of a efficient way to place the 'Yes' text in the 'Marker' column in table 'B', where for each record in table 'A' if the string text matches the prefix string text found in the 'Long_Name' string in table 'B'.
For example:
1.Get text 'euston' in column 'string_text_names' from table 'A'.
2.Check in table 'B', where for each record if the 'Long_Name' begins with 'euston' in the string text,
3. If yes, then add 'YES' text to the corresponding 'Marker' column in table B. If no, then move to the next record in the 'Long_Name' in table 'B'
4 Go through all the records in table 'B' on the 'Long_name' column and adding the 'YES' text in the 'Marker' column where the prefix texts both matches.
5.Repeat the process 1 to 4 again but with the next 'string_text_names' in table 'A' 'aldgate'.
6. Until all the 'string_text_names' records have been examined against the 'Long_Name' text in table 'B', then finally the process can end.
The FINAL table would look something like this.
Long_Name | Marker
-----------------------------
euston-32434 | YES
euston1 | YES
euston 1234 | YES
cat |
eaton |
bank245rfwef | YES
temple | YES
temple-1 | YES
temple-2 | YES
temple-234 | YES
baker |
robin |
leicester | YES
I think this needs to be done in VBA and not by MS Access macros, as one can't
determine how many 'string_text_names' there will be in the list.