Kevin Malloy Kevin Malloy - 1 year ago 53
SQL Question

Split delimited entries into new rows in Access

So someone gave me a spreadsheet of orders, the unique value of each order is the PO, the person that gave me the spreadsheet is lazy and decided for orders with multiple PO's but the same information they'd just separate them by a "/". So for instance my table looks like this

PO Vendor State
123456/234567 Bob KY
345678 Joe GA
123432/123456 Sue CA
234234 Mike CA

What I hoped to do as separate the PO using the "/" symbol as a delimiter so it looks like this.

PO Vendor State
123456 Bob KY
234567 Bob KY
345678 Joe GA
123432 Sue CA
123456 Sue CA
234234 Mike CA

Now I have been brainstorming a few ways to go about this. Ultimately I want this data in Access. The data in its original format is in Excel. What I wanted to do is write a vba function in Access that I could use in conjunction with a SQL statement to separate the values. I am struggling at the moment though as I am not sure where to start.

Answer Source

If I had to do it I would

  • Import the raw data into a table named [TempTable].
  • Copy [TempTable] to a new table named [ActualTable] using the "Structure Only" option.

Then, in a VBA routine I would

  • Open two DAO recordsets, rstIn for [TempTable] and rstOut for [ActualTable]
  • Loop through the rstIn recordset.
  • Use the VBA Split() function to split the [PO] values on "/" into an array.
  • For Each array item I would use rstOut.AddNew to write a record into [ActualTable]