仅使用工作日MS Access将周末插入表中

Question

我需要将周末插入只有工作日的表中，然后将最后一个已知值分配给周末值。我知道我将需要一个插入查询，尽管在VBA和SQL方面我还是很新。

到目前为止的代码：

Private Sub btnWeekends_Click()
DoCmd.SetWarnings False

Dim db as DataBase
Dim rs as RecordSet
Dim fieldCount as Integer
Dim i as Integer

set db = CurrentDb
set rs = db.OpenRecordSet("Archive", dbOpenDynaset)
fieldCount = db.TableDefs("Archive").Fields.Count

Dim DateVal As Date
DateVal = rs![ValDate]

Do While Not rs.EOF
    i = 0
    Do While IsNull(DLookup("ValDate", "Archive", "ValDate=#" & DateAdd("d", 1, ValDate) & "#")) = True
        rs.AddNew
        ' removed other fields and edited line below
        rs![ValDate] = DateVal
        rs.Update
        i = i + 1
    Loop
    rs.MoveNext
Loop

'//-----Clean Up
set db = Nothing
set rs = Nothing

DoCmd.SetWarnings True

End Sub

我拥有的数据（从周五开始）：

+------------------------------------------------------------------------+
|ID |Customer Name|  Nbr |   City   |Value of Day|ExtendedNbr|  ValDate  |
+------------------------------------------------------------------------+
|001|     Cust1   |  91  | New York |     529    | 91928592  | 1/5/2018  |
|002|     Cust2   |  87  | Las Vegas|     654    | 85642187  | 1/5/2018  |
|003|     Cust3   |  45  | Denver   |     258    | 78943245  | 1/5/2018  |

|004|     Cust1   |  91  | New York |     611    | 91928592  | 1/8/2018  |
|005|     Cust2   |  87  | Las Vegas|     753    | 85642187  | 1/8/2018  |
|006|     Cust3   |  45  | Denver   |     357    | 78943245  | 1/8/2018  | 
+------------------------------------------------------------------------+
'ValDate then skips past 1/6/2018 and 1/7/2018 to 1/8/2018

我需要的数据：

+------------------------------------------------------------------------+
|ID |Customer Name|  Nbr |   City   |Value of Day|ExtendedNbr|  ValDate  |
+------------------------------------------------------------------------+
|001|     Cust1   |  91  | New York |     529    | 91928592  | 1/5/2018  |
|002|     Cust2   |  87  | Las Vegas|     654    | 85642187  | 1/5/2018  |
|003|     Cust3   |  45  | Denver   |     258    | 78943245  | 1/5/2018  |

|004|     Cust1   |  91  | New York |     529    | 91928592  | 1/6/2018  |
|005|     Cust2   |  87  | Las Vegas|     654    | 85642187  | 1/6/2018  |
|006|     Cust3   |  45  | Denver   |     258    | 78943245  | 1/6/2018  |

|007|     Cust1   |  91  | New York |     529    | 91928592  | 1/7/2018  |
|008|     Cust2   |  87  | Las Vegas|     654    | 85642187  | 1/7/2018  |
|009|     Cust3   |  45  | Denver   |     258    | 78943245  | 1/7/2018  |

|010|     Cust1   |  91  | New York |     611    | 91928592  | 1/8/2018  |
|011|     Cust2   |  87  | Las Vegas|     753    | 85642187  | 1/8/2018  |
|012|     Cust3   |  45  | Denver   |     357    | 78943245  | 1/8/2018  |
+------------------------------------------------------------------------+
'I'm needing it to add the Saturday(1/6/2018) and Sunday(1/7/2018) before continuing on to 1/8/2018

因为没有Value of Day对1/6/2018或者1/7/2018，我需要抢前一天的值，并将其输入到新添加的周末日期（例如，2018年1月6日和2018年1月7日）与匹配的信息，以及（Customer Name，Nbr，City，ExtendedNbr）。

在此先感谢您提供的任何帮助/建议。我仍然很新，任何学习更多的机会都将不胜感激！

编辑1：

rs.Update带有“运行时错误‘3022’的行错误：对表的请求更改未成功，因为它们会在索引，主键或关系中创建重复的值…”

编辑2：

结果在所有记录的末尾输入。由于某种原因，循环是无限的。新日期未正确插入表格中（它们都是相同的日期，并且顺序不正确）。

Answer 1

这是一个基于SQL的解决方案。

这将获得所有记录，其中客户x存在星期五+下星期一的记录，但不包括星期六的记录。

SELECT a1.*
FROM Archive a1
INNER JOIN Archive a2 ON (
       (a1.Nbr = a2.Nbr) 
   AND (a1.ExtendedNbr = a2.ExtendedNbr) 
   AND (a1.ValDate + 3 = a2.ValDate) 
   AND (DatePart("w", a1.ValDate) = 6)
  )
WHERE NOT EXISTS
(SELECT * FROM Archive a3 
 WHERE a3.Nbr = a1.Nbr
 AND a3.ValDate = a1.ValDate + 1)

您可以使用它来插入星期六：

INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate)
SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1
FROM Archive a1
INNER JOIN Archive a2 ON (
       (a1.Nbr = a2.Nbr) 
   AND (a1.ExtendedNbr = a2.ExtendedNbr) 
   AND (a1.ValDate + 3 = a2.ValDate) 
   AND (DatePart("w", a1.ValDate) = 6)
  )
WHERE NOT EXISTS
(SELECT * FROM Archive a3 
 WHERE a3.Nbr = a1.Nbr
 AND a3.ValDate = a1.ValDate + 1)

要插入周日，使用相同的，但取代+ 1通过+ 2在这两个地方。

要插入随机的单个失踪日（银行假期），请更改a1.ValDate + 3为a1.ValDate + 2，然后删除AND (DatePart("w", a1.ValDate) = 6)

编辑

如果DatePart()在JOIN内遇到麻烦，则为备用版本：

INSERT INTO Archive ([Customer Name], Nbr, City, [Value of Day], ExtendedNbr, ValDate)
SELECT a1.[Customer Name], a1.Nbr, a1.City, a1.[Value of Day], a1.ExtendedNbr, a1.ValDate + 1
FROM Archive a1
INNER JOIN Archive a2 ON (
       (a1.Nbr = a2.Nbr) 
   AND (a1.ExtendedNbr = a2.ExtendedNbr) 
   AND (a1.ValDate + 3 = a2.ValDate) 
  )
WHERE NOT EXISTS
(SELECT * FROM Archive a3 
 WHERE a3.Nbr = a1.Nbr
 AND a3.ValDate = a1.ValDate + 1)
AND (DatePart("w", a1.ValDate) = 6)