将分隔的条目拆分为Access中的新行 [英] 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

现在，我一直在集思广益地解决此问题.最终，我希望在Access中获得这些数据.原始格式的数据在Excel中.我想做的是在Access中编写一个vba函数，该函数可以与SQL语句结合使用以分隔值.我目前仍在努力，因为我不确定从哪里开始.

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.

推荐答案

如果必须这样做，我会

• 将原始数据导入到名为[TempTable]的表中.
• 使用仅结构"选项将[TempTable]复制到名为[ActualTable]的新表中.

然后，在VBA例程中，我会

Then, in a VBA routine I would

• 打开两个DAO记录集，对于[TempTable]打开rstIn，对于[ActualTable]打开rstOut
• 遍历rstIn记录集.
• 使用VBA Split()函数将"/"上的[PO]值拆分为一个数组.
• For Each数组项，我将使用rstOut.AddNew将记录写入[ActualTable]

• 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]

这篇关于将分隔的条目拆分为Access中的新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！