Going forward there are more and more systems that have somewhat un-formated text or memo fields. It can be useful to tag fields. Here’s a collection of 2 functions with a script to pull them together designed to create a junction table.
What’s nice about it is that it could be used in lots of situations as a nightly process to tag manually input notes to help assist users navigate screeds of text.
This code is generalized and would need to be adapted for your specific table and field names
In mine you will need 4 tables
T001TableContainingFieldtobeCatetgorized – as per title it has a field called PKID and a field Called Text which is the memo field against which the SQL compares categories
T002Category – table that contains the categories that are compared against the text field
T003JunctionTable – the junction table that will contain the links between our notes table and the category table.
T004SQL – table to contain update queries – the field storing the strings is SQLstring – RunQueriesFromTable uses the SQLstring query and places the result in T003JunctionTable
The function RunQueriesFromTable is a previous function I wrote
'Make sure the name of the recordset is unambiguous
'Good practice to reference the actual library
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim SQLUpJunc As String
strQuote = Chr$(34)
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT * FROM T001TableContainingFieldtobeCategorized")
Set rs2 = db.OpenRecordset("T004SQL")
Set rs3 = db.OpenRecordset("T002Category")
'the data source can be a Table Name a query name or an sql string
'it would be possible to change the SQL to set to another set of records
'Check to see if there are any records in the set
If Not (rs3.EOF And rs3.BOF) Then
'there are no records if End of File and beginning of file are both true
Do Until rs3.EOF = True
SQLUpJunc = "INSERT INTO T003JunctionTable ( FKIDT001, FKIDT002 ) SELECT T001TableContainingFieldtobeCategorized.PKID, " & rs3!PKID & " AS FKIDT002 FROM T001TableContainingFieldtobeCategorized WHERE (((T001TableContainingFieldtobeCategorized.Text) Like " & strQuote & "*" & rs3!Category & "*" & strQuote & "));"
rs2!SQLstring = SQLUpJunc
'MsgBox "No Records available for updating exit sub"
'MsgBox "Looped through the records and updated table of SQL"
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set db = Nothing
'remember to break an infinite loop press ctrl + break
Public Function RunQueriesFromTable(SQLSource As String)
Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL As String
StartTime = Now()
Set rstZ = CurrentDb.OpenRecordset(SQLSource)
Do Until rstZ.EOF
strSQL = rstZ!SQLstring
EndTime = Now()
'MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime
Public Function CreateJunctionTable()