合并具有连续日期的记录 [英] Merging records with consecutive dates

问题描述

在使用Access 2007作为前端并且使用SQL2005作为后端的数据库中，我遇到以下问题.

I have the following problem in a Database using Access 2007 as front end and SQL2005 as back-end.

在表A中，我具有以下数据结构

In Table A I have the following data structure

Table A

ID     Date            Supplier_ID   
1      10/22/2009      1              
2      10/23/2009      1             
3      10/24/2009      2   
4      10/25/2009      2           
5      10/26/2009      1

我需要合并具有连续日期和相同Supplier_ID的值，为这些连续记录中的每一个增加一个新列(天)，然后将数据写入表B，这样我就拥有

I need to merge values that have consecutive dates and the same Supplier_ID, increment a new column (Days) for each of these consecutive records and them write the data into Table B, so that I have

Table B

ID     Date            Supplier_ID      Days
1      10/22/2009      1                2
2      10/24/2009      2                2
3      10/26/2009      1                1 

仅可以合并连续几天.因此，表A中的ID 5已作为新记录添加到表B中.自从我与Access VBA合作以来已经有一段时间了，我想知道解决该问题的正确方法.

Only consecutive days should be merged. Hence ID 5 in table A has is added to table B as a new record. It's been some time since I've been working with Access VBA and wondered what the right approach to this would be.

推荐答案

您有很多业务规则，但有一些独特的假设.例如，TableA从不为空，TableB始终为空

You have a lot of business rules with some unique assumptions. Eg TableA is never empty, TableB is always empty before you run this

无论如何，下面的代码将使用您的示例数据进行工作:

Regardless, the code below will work using your sample data:

Dim rs As Recordset
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb

Dim Supplier_ID As Integer
Dim Days As Integer
Dim FirstDate As Date
Set qdf = dbs.CreateQueryDef("", "select [Date], Supplier_ID from tableA order by [date] ")
Set rs = qdf.OpenRecordset()

Supplier_ID = rs!Supplier_ID
FirstDate = rs!Date
While Not rs.EOF
    If rs!Supplier_ID <> Supplier_ID Then
        If Supplier_ID <> 0 Then
            ' we don't want to insert the first time we run through this, so we check if Supplier_ID is not zero
            dbs.Execute ("insert into tableB ([Date], Supplier_ID, Days) select #" + Format(FirstDate, "dd-mmm-yyyy") + "#, " + Str(Supplier_ID) + ", " + Str(Days))
            Supplier_ID = rs!Supplier_ID
            FirstDate = rs!Date
            Days = 0
        End If

    End If
    Days = Days + 1
    rs.MoveNext
Wend

dbs.Execute ("insert into tableB ([Date], Supplier_ID, Days) select #" + Format(FirstDate, "dd-mmm-yyyy") + "#, " + Str(Supplier_ID) + ", " + Str(Days))

虽然可以做到，但Access(通常是SQL)并不是进行逐行比较的最佳方法.即使您可以使用光标或类似上面示例的代码，维护可能是一个挑战！

While you can do it, Access (and generally SQL) isn't the best for doing row by row comparisons. Even though you can use cursors or code like the example above, maintenance can be a challenge!

这篇关于合并具有连续日期的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！