How to Create String Aggregates in Microsoft Access

String aggregates allow us to make a query that will have many rows of one field aggregated into a single, delimited field. For example, a list of cars with available colors, a class with a list of student names, a hamburger with a list of toppings and more. Many popular databases like SQL Server and SQLite include a function for this type of query, but MS Access does not. In this episode I will demonstrate one way to write this function in VBA, so that you can create queries with delimited list fields.
Related Videos:
How to Use a Pass Through Query in MS Access - SQL Server Example
• How to Use a Pass Thro...
How to Simulate Lag and Lead Window Functions
• How to Simulate Lag an...
How to Filter by Month in MS Access Queries, Forms, and Reports
• How to Filter by Month...
How to Use Reference Expressions to Get Form Values in MS Access
• How to Use Reference E...
How to Create String Aggregates in Microsoft Access
You are watching this video now!
Join me on Patreon!
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.co...
Want the code from this video?
mackenziemacken...
Interested in transforming your career or finding your next gig?
system.billzon...
Want my team to do a project for you? Let's get to it!
system.billzon...
Contact form you can find at www.mackenziema...
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksyne...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy....
#msaccess #vba

[@seanmackenziedataengineering]

IMPORTANT NOTES: 🔹 If you anticipate having NULLS in your query or get related errors, just add *On Error Resume Next* on a new line after the Dim statements in the function. It will return an empty string instead of an error. 🔹 This is the "easy but slow" version of this procedure which is great for small numbers of records or for use on a form to calculate and view each time a new record is presented. It will be slow in processing large numbers of records because a query must be run on each row. For a fast version of this procedure that is a bit harder to program but gives great results, check out my added material on this topic on Patreon to make it 100x faster: www.patreon.com/posts/speeding-up-with-73513312?Link& 🔺 Note that this procedure uses dynamic SQL which is fine for data wrangling, reporting, or personal use. For customer facing solutions, I would recommend converting this to use parameters for safety.

[@miles6875]

Great video Sean. Effective code and elegantly drafted as well. These are the kinds of videos that take the efficiency/shareability of one’s code to an entirely different level. Big fan of your work

[@seanmackenziedataengineering]

Thanks, much appreciated!!

[@bobykumar3960]

i like the way u say " i am your host sean mackenzie".

[@seanmackenziedataengineering]

Really? I was going to cut that from future videos to save time lol!

[@tutsecret499]

I thought about the same. But did not tell him yet.

[@tutsecret499]

@@seanmackenziedataengineering Don't cut. It's your brandmark.

[@jalaluddin8276]

Interesting video. Could be useful to develop a module for other manufacturers such as carpets, tiles etc.

[@seanmackenziedataengineering]

That's a great idea!

[@mirojobu]

Dynamite!

[@seanmackenziedataengineering]

Thanks!

[@RungeCarl]

As always brilliant!

[@seanmackenziedataengineering]

Thanks!

[@SHCSBaker]

Great video! How about one on field mapping (using a table to store mapping) when importing csv files.

[@seanmackenziedataengineering]

Thanks for that suggestion! I'll add it to my list.

[@gerfer6261]

💯

[@seanmackenziedataengineering]

Thanks!

[@Hassanakora]

Kindly make video on school fees system

[@seanmackenziedataengineering]

Good idea for a small system. That could be a video series perhaps.

[@Hassanakora]

@@seanmackenziedataengineering thanks that will help me a lot