Split a Field into Multiple Individual Child Records

In this Microsoft Access tutorial, I'm going to teach you how to take a single field that has multiple part numbers in it and split that up into individual records in a child table. This is an advanced Developer tutorial, and we will use nested recordset loops and the split function with a string array.

Lucas from Minneapolis, Minnesota (a Gold Member) asks: Estimators fill out a spreadsheet while they're on the road with a list of job IDs and part numbers. When they send me the spreadsheet, it's got the job number on a row, but then the part numbers are all listed in one field, comma separated. Of course, I have to type these in manually into my database because I've got proper table relationships set up. So, how can I go about taking those part numbers and splitting them up into individual records in a child table?

Members

There is no Extended Cut, but here's the database for the Gold members.

•  Database Download - Gold Members

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Prerequisites

• Intro to VBA: https://599cd.com/IntroVBA
• Split Function: https://599cd.com/SplitFunction
• Using Recordsets: https://599cd.com/Recordset

Links

• Many-to-Many: https://599cd.com/M2M
• Status Box: https://599cd.com/Status

Recommended Courses

• Recordsets in Access Developer 16: https://599cd.com/ACD16
• Arrays in Access Developer 21: https://599cd.com/ACD21

Learn More FREE Access Beginner Level 1 FREE Access Quick Start in 30 Minutes Access Level 2 for just $1 Free Templates TechHelp Free Templates Blank Template Contact Management Order Entry & Invoicing More Access Templates

Resources Diamond Sponsors - Information on our Sponsors Mailing List - Get emails when new videos released Consulting - Need help with your database Tip Jar - Your tips are graciously accepted Questions? Please feel free to post your questions or comments below or post them in the Forums.

Keywords

access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Recordsets, recordset loop, nested recordsets, split function, string array, nested recordset loop, break items in field up into individual records