MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

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

Function CategorizeField()

'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

rs3.MoveFirst

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 & "));"

With rs2
.AddNew
rs2!SQLstring = SQLUpJunc
rs2.Update
End With

rs3.MoveNext
Loop
Else
'MsgBox "No Records available for updating exit sub"
Exit Function
End If
'MsgBox "Looped through the records and updated table of SQL"

rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing
Set db = Nothing


'remember to break an infinite loop press ctrl + break

End Function
Public Function RunQueriesFromTable(SQLSource As String)

DoCmd.SetWarnings False

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
DoCmd.RunSQL strSQL
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

'MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function
Public Function CreateJunctionTable()

Call CategorizeField
Call RunQueriesFromTable("T004SQL")

MsgBox "Finished"



End Function

Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Simple but can be useful

Useful if you are wanting to open multiple tabs in a browser window at the same time

@echo off
start "Simple Search" "https://planning.westlothian.gov.uk/publicaccess/search.do?action=simple&searchType=Application"
start "Google Maps " "https://www.google.co.uk/maps/@55.8625775,-3.6759593,17z"
start "WLC Spade" ;
start "idoxEDRMS Login" "http://cc-dmsapp-01:8080/IDOXSoftware/secure/IG_Main?url="

Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

There are a whole host of applications in which you may wish to record the biological or legal relationships between individuals. Here is a short investigation of some of the subtleties related to the options you have to model this data.

The actual biological parent to biological child relationship is somewhat more subtle than the classic description in books would have you believe (usually these equate the parent child relationship as a one to many relationship.

Strictly speaking biological parent to biological child is a many to many relationship (which in MS Access is a recursion of 2 one to many relationships) but it is a specific version of the many to many relationship. One parent can have many (read infinite) children but one child can only ever have 2 parents – one biological mother linked to one biological father (ignoring latest developments in science). Of course a one to two relationship is still a type of of one to many relationship it is a more specific type of one to many relationship but one to many nonetheless – so to model all relationships you still need two one to many links. If you consider the unique combination of mother and father as a group in itself then you can model all relationships again as only consisting of one group of one to many relationships rather than as two one to many relationships (which we presently have defined as one one to many and one one to two relationship). To do this you would have to make a mother and father a unique pair who can have infinite number of children but a child can ONLY have one mother father pair.

Many to many relationship

To a certain extent this looks like a one to many relationship as there appears to be only two tables (albeit one being aliased). This is correct but to record a dependents relationship with both parents two separate records with separate PKIDs need to be input into the persons junction table actually making it a many to many relationship.

By viewing the parents PKIDs as a group we can convert this many to many relationship back to a one to many relationship by adding a further field into the persons junction table. Now the relationship between a dependent and its mother and father can be recorded in one record in the personsjunctiontable.

It is not initially clear but a one to many relationship where the many is a finite number (ie not infinite – in our example 2) – can be re-modelled as a one to one relationship as follows.

In fact one thing that I learnt when I started reading about relational databases is that relational not only relates to the relationship between tables but the relationship within tables.

Thus we can separate a table of columns back out into a one to many relationship by pivoting out the column names and making them a value within a field themselves linking them to a field and then adding the ability to add a value. This works because the number of columns is finite. I believe this is the thinking behind a key value database.

Looking at the tables resulting from data input you would get

What makes this particularly good is that a lot of attributes can be stored against an individual and if you forget a column name you simply add it to the field name table and it becomes an additional option in the KeyValueData table. This can be particularly good if you are not sure of the set of column names that your users may wish to use. It has the disadvantage that validation will become harder as the values are often a mix of value types and cannot be so easily tied back to a particular list as the source of the field will change according to the value of its relative field name. Here for example I could easily add Mother and Father to the table of field names but a user would simply type these in rather than having them validated as per the first three relationship structures.