如何在Microsoft Access中将含垂直发票的单个表格拆分为多个表格

步骤1：添加发票分组辅助列 #

先给原表新增一个数字类型字段InvoiceGroup，用于标记同一张发票的所有记录。通过VBA遍历表中数据，识别连续8个空白行作为发票分隔符，给同组发票记录分配相同的分组编号：

Sub AssignInvoiceGroups()
    Dim rs As Recordset
    Dim blankCount As Integer
    Dim groupNum As Integer
    Dim isBlank As Boolean
    Dim i As Integer
    
    Set rs = CurrentDb.OpenRecordset("YourOriginalTableName") ' 替换为你的原表名
    blankCount = 0
    groupNum = 1
    
    Do While Not rs.EOF
        isBlank = True
        ' 检查当前行是否为空白行，根据表字段调整检查范围
        For i = 0 To rs.Fields.Count - 1
            If Not IsNull(rs.Fields(i).Value) And Trim(rs.Fields(i).Value) <> "" Then
                isBlank = False
                Exit For
            End If
        Next i
        
        If isBlank Then
            blankCount = blankCount + 1
            ' 连续8个空白行，切换到下一个发票组
            If blankCount >= 8 Then
                groupNum = groupNum + 1
                blankCount = 0
            End If
        Else
            blankCount = 0
            rs.Edit
            rs!InvoiceGroup = groupNum
            rs.Update
        End If
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    MsgBox "发票分组标记完成"
End Sub

运行前记得替换YourOriginalTableName为实际表名，调整空白行的字段检查逻辑匹配你的数据结构。

步骤2：将垂直发票数据转成水平结构 #

由于发票是垂直格式（字段名和对应值按行存储），用交叉查询把每个分组的垂直行转成水平的完整发票记录：

TRANSFORM First(YourOriginalTable.ValueColumn) ' ValueColumn是存储字段值的列
SELECT InvoiceGroup
FROM YourOriginalTable
WHERE InvoiceGroup IS NOT NULL 
AND FieldNameColumn IS NOT NULL ' FieldNameColumn是存储"发票号""客户名"等字段名的列
GROUP BY InvoiceGroup
PIVOT FieldNameColumn;

替换ValueColumn和FieldNameColumn为表中对应的列名，运行查询后会得到每行对应一张发票的规范化数据。

步骤3：生成独立表格（可选） #

如果确实需要每个发票对应一个独立表格（不推荐，数据库规范化建议用单表存储所有发票），可以用VBA循环分组创建表并插入数据：

Sub CreateIndividualInvoiceTables()
    Dim rsGroups As Recordset
    Dim db As Database
    Dim sql As String
    Dim groupNum As Integer
    
    Set db = CurrentDb
    ' 从步骤2的交叉查询结果中获取所有分组
    Set rsGroups = db.OpenRecordset("SELECT DISTINCT InvoiceGroup FROM CrossQueryResult") ' 替换为你的查询名
    
    Do While Not rsGroups.EOF
        groupNum = rsGroups!InvoiceGroup
        ' 创建新表，根据实际发票字段调整结构
        sql = "CREATE TABLE Invoice_" & groupNum & " (" & _
              "ID AUTOINCREMENT PRIMARY KEY, " & _
              "InvoiceNumber TEXT(20), " & _
              "CustomerName TEXT(50), " & _
              "InvoiceDate DATE, " & _
              "TotalAmount CURRENCY)"
        db.Execute sql
        
        ' 插入该分组的发票数据
        sql = "INSERT INTO Invoice_" & groupNum & " (InvoiceNumber, CustomerName, InvoiceDate, TotalAmount) " & _
              "SELECT InvoiceNumber, CustomerName, InvoiceDate, TotalAmount " & _
              "FROM CrossQueryResult WHERE InvoiceGroup = " & groupNum
        db.Execute sql
        
        rsGroups.MoveNext
    Loop
    
    rsGroups.Close
    Set rsGroups = Nothing
    Set db = Nothing
    MsgBox "独立发票表格创建完成"
End Sub

步骤4：清理与验证 #

• 删除原表中的空白行，或保留原表作为数据备份；
• 核对新生成表格的数据准确性，确保字段对应无误。

内容的提问来源于stack exchange，提问作者Angel Viera