Split Part Numbers
By Richard Rost
3 years ago
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.
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
Links
Recommended Courses
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
Intro In this video, I will show you how to split comma-separated part numbers from a single field in Microsoft Access and create individual records for each part number in a related child table. We will work through an advanced example using nested recordset loops and the Split function to efficiently handle this common data import problem, with tips on error handling and working with arrays. This is an advanced developer tutorial designed for users comfortable with VBA programming in Access.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to teach you how to take a single field that has multiple part numbers in it and split that field up into individual records in a child table. This is going to be an advanced developer tutorial.
We are going to use nested recordset loops and the Split function with a string array. Lots of cool stuff in this video today for the advanced folks.
Today's question comes from Lucas in Minneapolis, Minnesota, one of my Gold members. Lucas says estimators fill out a spreadsheet while they are on the road with a list of job IDs and part numbers.
When they send me the spreadsheet, it has 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 have got proper relationships set up. So how can I go about taking those part numbers and splitting them up into individual records in a child table?
I have been talking to Lucas, and he has no control over these estimators. They work for a different company. So that is the format that they put the stuff in their spreadsheet with, and they send him this spreadsheet. It has hundreds of rows in it. He wants to take those and split them off. He has a job table and a part number table, like a sub table, which you should have, but that is not how he gets the data from his estimator. So how do we fix this?
Well, we can use a recordset loop to loop through each of the job records. Then we will use the Split function to pull those numbers out since it is comma-separated. That is nice. We can easily use the Split function. Then we will use a second recordset inside that to loop through and add each of those.
You can see Enterprise D there, for example, has four child records. So we will define it and void jog it too. That will all happen in our recordset loop.
As I mentioned at the top of the video, this is a developer-level video. This is going to be some advanced developer stuff. So if you are new to programming, if you have never programmed in VBA before, go watch this first - about 20 minutes long. It will start to teach everything you need to know, and then come on back.
You will also need to know how to use the Split function. That is where you can take a string and break it up into multiple components based on some delimiter, like a comma. I showed you how to do it before with an address where you get the whole address in one string and you can separate that out into city, state, and zip and such. So go watch this video too.
Finally, go watch my recordset video. This is an older one, but it is an oldie. I have to re-record this guy. It is a couple of years old. It is only two years old. It is not that bad. It actually comes from one of my classes, Access Developer 16, where I cover recordsets. So go watch this if you have never used a recordset before. A recordset is basically a way to loop through the records in a table. So you can say start at job one, go to job two, go to job three, and so on. You can also use them to add records to a table. So go watch this first.
These are all free videos. They are on my website. They are on my YouTube channel. You will find links down below in the description. But watch those and then come on back.
All right, so here I am in my TechHelp free template. This is a free database. You can download it on my website if you want to, but this will work with any database you want.
So I have got a sample jobT all set up. This is basically kind of what Lucas gets. He has a job ID. He has a description. He has a bunch of other fields in here - who the sales rep is and the client, etc. All we really care about for this example is this. The part numbers are all in one text field and they are separated by commas.
Now I just use numbers in mine, but his part numbers have letters in them too. So the part number is not really a number. Do not you love when people do that - they have a part number and it has got letters in it. It is like in the old days when phone numbers had letters in them.
We are going to take these and split them off and put them in a separate table. I have already got that table built. Here it is right here, jobPartT, because we will assume you have a part number table. It has your parts in it, your part table.
So the jobPartT will be the junction table between the two in a many-to-many relationship. If you do not know what a junction table is or a many-to-many relationship, you have to go back a class. You have to go back to my Expert series. I covered this in the Expert series, so go back and watch this.
I do not have a part table in this database, but this would be the junction table. We are going to put the job ID here and then the part number here, and that is coming out of here. In fact, let us keep everything the same. I had it put in here as partID number. Let us make this part number and make this short text because I want to keep this exactly the same as this.
If you have got a part table that has got a part number and a part ID, that is fine. If you are dealing with other people's part numbers - J6241, that is their code - in your database, it might be autonumber 62. So it is okay to have both fields in your table, but we are going to peel off this part number and put it down here into this field.
Save all, what we got here. Close it down. All right.
So we got our main menu. Here it is. We have got our jobT that has got the part numbers in it. We have got an empty jobPartT.
Let us build some code. If you are not familiar with my database, go watch the link, Database Setup, and I have this thing in here called a status box, which I have a status function. I can click Hello World. If you want to learn how this was built, I will put a link to it down below as well. I get asked this all the time: hey, how did you make the status box? Well, here is a separate video for you.
But let us take this guy here and let us just use this to get some information first. Let us loop through those records in the job table. Loop through this. Let us see how many part numbers are in there and get some information first.
So let us right-click, build event. We are not going to status Hello World. What are we going to do? Well, first, let us blank the status box. StatusBox equals nothing. Let us clear it up, just in case there is something there from a previous run.
We need a recordset: Dim rs as Recordset. What are we setting that equal to? Set rs = CurrentDB.OpenRecordset. What are we opening? We are reading this one. We are going to read jobT. We are going to loop through all the records.
So while not rs.EOF - that stands for End Of File, going back to file handling - do some stuff, then go to the next record and end your While loop. I like to put the shell of it first because if I do not, I always forget the rs.MoveNext and then I am stuck in an endless loop. I do that first.
Then we close up shop: rs.Close, Set rs = Nothing, and we are good. We could be done right now. Hit the button; it is going to happen. But that is our proper shell there.
We have got the framework of our code in place. In here, let us just message box, or instead of message box, let us status what each record is. So we are going to status, and we are going to say, you know, "Record: " & rs!Description, just like that. Show me what the description is because that is the field: rs!Description. This guy is showing me each one of these guys.
Save it. Let us throw a quick Debug - Compile in there just to make sure our code is good, and it is. Of course, I wrote it.
Close it up. Open it back up again. Hit the button. I think there are three records there: Voyager, Defiant, and Enterprise. Remember these are coming backwards.
Unless you put a sort in here, like a SELECT with a sort, you cannot control what order they come in. Really, they come in the order that they exist in the table, but not always. Do not rely on that.
Now in this table, I also have a part number field that I want to split up based on that comma. So I am going to use an array just like we did in the other video.
Let us Dim partArray as String. I have created an array of strings. How many items are in it? Oh, no, no, no. Could be zero. Could be a thousand. It is an open-ended array. I just realized I do not have a TechHelp video about arrays. I will have to make one. But I do cover them in detail in Access Developer 21.
Now that we have an array set up, I can say right here: partArray = Split(rs!PartNumbers, ",") so inside quotes, put a comma. That is going to take the rs!PartNumbers field - the part numbers field, the current record that we are on - and run it through the Split function splitting it on the comma.
It does not do anything with it at this point. Let us just take a look and see how many items are in that array at this point. So let me put the status below that.
And I am going to say, how do we tell how many items are in an array? We use the UBound function. So it is UBound(partArray). Then you have to add one to it because it is zero-based. So if there is one item in it, it will return a zero.
Save it. Run it. Boom. 4, 4, 2. Which should be correct.
Now this always brings up the - you have to think about if your people who are using the database are going to break things. If you do have something with no part numbers in it and you try to run this, you get "Invalid use of Null," because you cannot run that on that because it is null. So we will do a little check right here.
If Not IsNull(rs!PartNumbers), then it is okay to do this stuff. Otherwise, do not do this stuff because you are not going to do anything if there is nothing in there. You could show the record and show "none" if you want to. It is up to you. But now we get past the error.
Always got to think about those pesky users. They are always trying to break stuff. You want to try to avoid throwing errors if possible. Remember, if you throw an error, all your global variables get reset. That is never a good thing. So either handle the error yourself, or put some error handling in there. I have got whole lessons on error handling too.
So where are we at? We have looped through each of the records, and we know we have got the part numbers split into an array. Now I want a second recordset so that I can write each one of those items off to that job part table.
So for each one of those, we are going to loop through and write them out as records.
We need a second recordset: rs2 as Recordset. You could do rsIn and rsOut or rs1 and rs2. Whatever - those are your Legos. Put them together however you want.
We are also going to need a loop variable. So x as Long. x is my go-to.
We are going to need a second recordset declaration, so Set rs2 = OpenRecordset("JobPartT"). One to read, one to write.
In here, we are going to split our guys up into the partArray. You can still display it if you want to. Now, here we are going to loop through each of those items and write them out to the table. What does that look like? We will use a For loop: For x = 0 to UBound(partArray)
And I forgot my To.
And then Next. What goes in the candy center here? Well, rs2.AddNew. rs2!JobID = rs!JobID. Set it to the same jobID. rs2!PartNumber. You do not always get the camel casing with the recordset elements. Sometimes it sees it, sometimes it does not. = partArray(x).
Then when we update the record: rs2.Update. That is it. That adds the new record. You could do this with an SQL INSERT statement if you prefer. It is actually a tiny bit faster, but for this, unless you are doing hundreds of thousands of records, you are not going to notice a difference.
Then this finishes up. The next record goes. Now we have to close up shop: rs2.Close, Set rs2 = Nothing.
There. Sorry, folks. It is late. I am tired. It has been a long day. I have been playing with OpenAI chat integration and trying to get that to work with Access, and I have got some great results. You guys are going to love this when I finish with it. You can integrate it into your Access database. So if you are writing out a letter, if you are using it as a contact manager, like I do - I write all my customer service emails in my Access database - hit one button, it will send it out to ChatGPT and bring it back with corrected text. That is going to be pretty cool. Then I am going to integrate that on my website, too.
Anyway, back to this. Here. Done. That's all.
Close it. Close it. Open it. Click the button. Let us see what we got. Here is our job table. Here is our job part table. Oh, look at that.
There you go. There is job one, item item item item, job two, item item item.
You can trim these, too, because some of these - I intentionally did not put spaces between them - so you might want to throw a Trim in there, too. Let us clear these out. Let us put a Trim in here right there. Or you could trim them as they go in there, however you want to do it.
Those are your Legos. This is going to save you some time and effort. There you go.
Now, of course, if these are actual numbers, you could then convert them back over to a number field, however you want to handle it.
If you love this stuff like I do, I love recordsets. When I first discovered recordsets years ago I was like, oh my god, this is amazing. I started off as a BASIC programmer back in 1980, so my brain thinks in loops. It took me a long time - I was probably using Access for a good 10 years before I really learned SQL, so anything I had to do I did it with a recordset. I just looped through the records and handled them like I just showed you in code with For loops and such.
SQL is more powerful for simple things that you put in SQL, but recordsets are much more powerful if you have to look at each record and analyze it and do different things and create child records and all that stuff.
If you really want to learn recordsets, I cover them in Access Developer 16, 17, 18. I have got tons and tons of recordset lessons. In this one, for example, I show you how to use a multi-select list box, which is where you have a list box, you can pick multiple items, we save those items to the table, load them back in, that kind of stuff. It is one of the only ways you can use multi-select list boxes. That is in Developer 16. You will find a link to that down below.
There you go. That is going to be your TechHelp video for today.
No extended cut today. Members, you guys have been spoiled. There is not much more I can do with this one; I already just gave you all the goods. But if you can think of anything else you want to see with this kind of stuff, let me know.
I had one other member ask me a very similar question. I might do a separate video on that same kind of technique, but we are not going to use the Split function. He gets emailed lists of email addresses and he wants to be able to split those up into separate records, too. I might do a separate video on that one next week. If you want to see that, let me know.
There you go. I hope that saves you some time, Lucas. I hope that is worth the cost of membership, which it should be. That was great stuff.
If I save you five minutes a day over the course of a year, how many minutes is that? That is a few minutes. That is like lunch. Actually, I just did the math: if I save you five minutes a day times five days a week, that is 25 minutes a week times, let us say, 50 weeks a year - I will give you two weeks off for vacation - that is 1250 minutes I am saving you over the course of a year. 1250 minutes divided by 60 is 20 hours. It is almost 21 hours if I save you five minutes a day from not having to type in these part numbers.
So if your Gold membership is worth 20 hours of your life every year - all right, what is a Gold membership right now? $99. So that is $2 an hour.
That is all for you. You can tell it is late and I am tired. That is all for today, folks.
I hope you learned something. Live long and prosper, my friends. I will see you next time.Quiz Q1. What is the main purpose of the video tutorial?
A. To show how to manually enter part numbers into a database
B. To teach how to split a comma-separated field into individual records in a child table
C. To explain how to create tables in Access
D. To demonstrate creating forms in Access
Q2. What format does Lucas receive the part numbers in?
A. Each part number on a separate row
B. Part numbers listed in separate columns
C. All part numbers in one field, comma-separated
D. Part numbers embedded within the job description
Q3. Why does Lucas need to split the part numbers into separate records?
A. He prefers to see one record per part number in Excel
B. His database has proper relationships that require individual part records
C. It is required by his boss
D. Access cannot read comma-separated values
Q4. Which VBA function is used to separate the part numbers into an array?
A. Break
B. Divide
C. Split
D. Parse
Q5. When looping through the job records, which object is used in VBA to access each record in the table?
A. Form
B. QueryDef
C. Recordset
D. Collection
Q6. What is the purpose of the UBound function when working with arrays?
A. Returns the first item in the array
B. Returns the position of a specific value in the array
C. Returns the number of items in the array (zero-based)
D. Converts array values to uppercase
Q7. What is the potential error if you try to Split a field that is Null?
A. Divide by zero error
B. Invalid use of Null
C. Stack overflow
D. Out of memory
Q8. What check should you perform before attempting to Split the part numbers field?
A. If Len(rs!PartNumbers) > 10
B. If rs!PartNumbers is numeric
C. If Not IsNull(rs!PartNumbers)
D. If the table has more than 100 records
Q9. After splitting the part numbers, where should each individual part number be stored?
A. In a new column of the main job table
B. In the jobPartT child (junction) table
C. As a new database file
D. Inside a temporary query
Q10. What is the main benefit of using recordsets for this process instead of purely SQL?
A. Recordsets allow for examining and manipulating each record individually
B. SQL is not supported in Access
C. Recordsets are always faster than SQL
D. Recordsets do not require VBA
Q11. What kind of relationship does the jobPartT table represent between jobs and parts?
A. One-to-one
B. Many-to-many
C. One-to-many
D. Recursive
Q12. If the part numbers in the source data have extra spaces, what should you do when writing them to the child table?
A. Ignore the extra spaces
B. Replace commas with semicolons
C. Use the Trim function to remove spaces
D. Delete the records
Q13. What programming construct is used for looping through array items when creating new child records?
A. Do While loop
B. For loop (For x = 0 To UBound(partArray))
C. Select Case statement
D. While Wend loop only
Q14. In the video, what is the recommended way to prevent an infinite loop when using a recordset?
A. Check for duplicates in the table
B. Make sure to include rs.MoveNext in the loop
C. Set rs = Nothing at the start of the loop
D. Use the status box for notification
Q15. Why is error handling considered important in this process?
A. To make the code run faster
B. To prevent users from breaking the code and global variables from being reset
C. To improve the user interface visuals
D. To automatically correct part numbers
Q16. For which users is this tutorial most appropriate?
A. Beginners with no programming experience
B. Intermediate to advanced Access developers
C. Microsoft Excel power users only
D. IT professionals with experience in C#
Q17. What specific technique is discussed for managing multi-select list boxes in Access?
A. Assigning them to primary keys directly
B. Using recordsets to load and save selected items
C. Exporting them to CSV through macros
D. Hiding them from the user interface
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-C; 9-B; 10-A; 11-B; 12-C; 13-B; 14-B; 15-B; 16-B; 17-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how you can take a single field in Microsoft Access containing multiple part numbers, all stored as a comma-separated list, and split that field into individual records in a related child table. This topic is aimed at advanced developers and focuses on using nested recordset loops and the Split function with string arrays.
The situation comes from a real-world scenario. Lucas, who works with estimators from another company, receives spreadsheets with hundreds of rows where each row contains a job ID and then a list of part numbers, all separated by commas in the same field. Unfortunately, this format is outside of his control, and he needs a way to reformat the data for proper use in his Access database, which has correct table relationships.
To solve this problem, we loop through each job record using a recordset. Then, we utilize the Split function to break apart the part numbers in that single field into an array of strings, each representing a part number. For each part number, we insert a new record into a child table, creating a proper one-to-many relationship. Effectively, we are transforming each comma-separated value into a separate record.
If this process or the language about many-to-many relationships or junction tables is unfamiliar to you, I recommend watching my earlier videos and lessons on these topics first. They are all free resources available on my website and YouTube channel. You may also want to check out my lessons on the Split function, which allows you to break up a single string into multiple components using a delimiter, like a comma. If recordsets are new to you, I also have an older tutorial on how recordsets work in Access, which you will find helpful.
In this example, we start with a table called jobT, mirroring what Lucas receives: it contains a job ID, description, and a field with all the part numbers crammed together. The target is to split these part numbers into a junction table, jobPartT. This junction table contains one record for each part number linked to its respective job, thereby following the proper relational model.
If your database includes a part table with both a part number and a part ID, that setup is fine. However, in this case, we are only concerned with extracting the part number provided in the spreadsheet and ensuring it gets recorded properly.
In the demonstration, I use a status box to show output information during the process. We start by creating a recordset to loop through all records in the job table. For each job, we check if the part numbers field is not Null (empty). The Split function is applied to this field, separating the part numbers wherever a comma appears. We check how many items result from the split using the UBound function, and to avoid errors, we handle the possibility of empty values.
Next, a second recordset is opened for the child table. We loop through each item in the part number array, and for each one, we add a new record with the job ID and the part number. You could add additional code to trim spaces off each part number as you insert them. This process can be easily tweaked depending on whether your part numbers involve text, numbers, or mixed data.
If you are curious about performance, this technique is very suitable for the scale described and will not present efficiency issues unless you are processing very large datasets. While it is possible to use SQL INSERT statements instead, recordsets offer a greater degree of flexibility and are easier to manage, especially when you need to examine or process each record individually.
Recordsets have always been one of my favorite tools in Access programming. For tasks that require more than a straightforward SQL query, they offer the flexibility to handle looped logic, make decisions, and create related records as shown here. I offer extensive coverage of recordsets in my Access Developer series, so if you want to deepen your understanding, be sure to check those out.
There is no extended cut for today's lesson since the entire process is already covered here. However, if you have related scenarios you want to see addressed—such as splitting email addresses into separate records—feel free to suggest them to me. I always appreciate new questions from members.
If you follow this approach, you can easily save yourself hours of tedious manual data entry every year, making your investment in mastering these techniques more than worthwhile.
You can find a complete video tutorial with step-by-step instructions for everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Splitting a comma-separated field into child table records
Using VBA recordset loops to process main table records
Applying the Split function to a text field in VBA
Creating and managing string arrays in VBA
Handling possible Null values in fields during processing
Looping through arrays to add records to another table
Writing new records to a child table with AddNew
Associating parent and child records through JobID
Trimming whitespace when splitting field values
Preventing errors from missing or empty data fields |
