VBA code - ConcatRelated by Allen Browne

First of all, Allen Browne is the saving grace of many database techs.  Allen Browne seems to live a very quiet life these days, but he has some big database products and Access related books to his name.  He is also one of about 70 people to be awarded Microsoft's Most Valuable Professional award in Access.  His website, www.allenbrowne.com, has his personal bio as well as plenty of free VBA code to assist Access users.  These VBA codes are life savers!

(If you're not familiar with custom functions, they are sets of VBA code saved to a specific database that can be called from anywhere within that database.  Skip to the end for a brief how-to on using custom functions)

My first exposure to Allen Browne was through his ConcatRelated function.  I had been dealing with a major data transfer to get 2 years of maintenance records from one db to another.  The source database was built to be big and fast, and the new database was more focused and nimble.  Because of this, the structures were quite different.  One of several obstacles I had to overcome was the structure of the "comments" field.

In the source database, the comments were split into smaller records as they were saved.  Each record contained no more than 250 characters worth of the comment and had a Sequence ID field to put them back together.  This worked great for storage space, but concatenating the comments back together became a problem.  The new new db was set up differently, using a Memo field with plenty of space for any comment that would need to be entered, so we had to put the comments back together again.

A major part of this obstacle was that the source db had a frontend program that we couldn't change, or even see.  I had no idea what code was being used to bring the records together and no way of finding out, but if you looked at the comment through the program it was always concatenated back together.

First I tried using a set of queries to stack the comments row-wise, instead of column-wise.  I gave myself kudos for the creativity, but this is definitely more spreadsheet thinking than database thinking.  The process would have required me to output the query as crosstab, export to CSV, get rid of the commas (but only the ones between comment cells, not after the workorder ID), bring it back into excel as comma delimited, and finally get it back into an Access table, all without losing any data from the massive outputs and inputs.  It was far from ideal.

Next, I tried to run a set of queries to concatenate each sequence 2 to sequence 1, then each sequence 3 to 1&2, and on until the longest comment (67 entries long) had been fully concatenated.  I was starting to worry this was the only way.

Now, this was early on in my database career, so I wasn't yet privy to the vast resources available for database professionals.  I hadn't heard of Allen Browne, I hadn't even stumbled on any of several helpful message boards directly focused toward MS Access help.  A coworker showed me the ConcatRelated function and saved my project.


This function took a little effort to get into correct syntax (mostly on the third argument), but it was well worth the minutes spent.  The ConcatRelated function is used like:

ConcatRelated("OrderDate", "tblOrders", "CompanyID = " & [CompanyID])

where Argument 1 is the field to be concatenated, Argument 2 is the table where the field is located, and Argument 3 is the grouping or filtering criteria.  Argument 4 is optional to specify a sort order (which I used), and Argument 5 is optional to specify what character(s) will be used to separate entries.

After my hours (probably days, actually) wasted trying to make my brute strength method work, I ended up with a simple query to give me the full result I needed and append it to a pre-designed table.

INSERT INTO [Mtr wocomment] ( WORKORDERID, COMMENTS )
SELECT [Mtr wocomment 3].WORKORDERID, ConcatRelated("[COMMENTS]","[Mtr wocomment 3]","[WORKORDERID] = '" & [WORKORDERID] & "'","[SEQID]","") AS COMMENTS
FROM [Mtr wocomment 3]
GROUP BY [Mtr wocomment 3].WORKORDERID;
 
The ConcatRelated function can be found on Allen Browne's website, at http://allenbrowne.com/func-concat.html.

Happy querying,

Tyler



To save a custom function to your database, go to your Create tab, and under Macro there's a drop-down menu where you can select Module.  Any custom functions you find on the web should be ready to copy and paste from right here.  You can compile the code from the Debug menu, then save the function using a name other than the name of the function!  This is the most common error.  If your module name matches your function name, you will get an error when you call the function.  You could parse the code to find out what arguments to type into the function, but most will come with instructions.  You will always call the function by using FunctionName(argument), with the function name and argument differing by function.  Just check your instructions, and they should be able to guide you through the use of the function.

Intro

My name is Tyler, and I'm an analyst, a database administrator, and a general data and technology specialist for a local water utility company.

As an analyst, I work with fairly small data sets to find trends and patterns.  In water utilities, this can mean reviewing break rates on water pipes, customer water usage trends, or department efficiencies.  There's no one-size-fits-all tool for analytics, so you'll see me using anything from Excel (most common) to Tableau or r.  I'm finding new analytical resources weekly, so I'll share my favorites here.

As a database administrator, I handle a wide range of responsibilities.  Some databases I've created from scratch, including table and user interface design.  Others I monitor on a SQL server and consult with departments about their data entry techniques.  Still others I handle reporting to go along with the analytics noted above.  Most often this is done in Access simply for ease-of-access (sorry for the pun) when I don't have the full admin rights of our Tech Services department.  I've found some great tools in Access during the last year, and since I'm fairly new to the world of database management, I'll share some of these tools in more basic language here.

Since I'm also one of the data junkies and techies in my department, I get called on for a lot of troubleshooting, laptop setup, and software recommendations.  While these aren't the norm, I still find some great tools from time to time that I'll also share here.

Subscribe to be notified of new tools as I find them, or just look for me on your next Google search when you've hit a brick wall.

May your data be clear and concise,

Tyler

PS: Mine Datum is the term used for a point 1,000 meters below sea level, typically used as a rule of thumb for the deepest a mineshaft should run.  The implied pun is entirely intended.