仅使用工作日MS Access将周末插入表中 [英] Inserting Weekends into Table with only Weekdays MS Access
问题描述
我需要将周末插入只有工作日的表中，然后将最后一个已知值分配给周末值.我知道我将需要一个插入查询，尽管在VBA和SQL方面我还是很新.
I'm needing to insert the weekends into a table that only has weekdays and then assign the last known value to the weekend values. I know I'm going to need an Insert Query, although I'm still pretty new when it comes to VBA and 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
我拥有的数据(从星期五开始):
Data that I have(starting on Friday):
+------------------------------------------------------------------------+
|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
因为对于1/6/2018或1/7/2018没有Value of Day，我需要获取前一天的值并将其输入到新添加的周末日期中(例如1/6/2018和1/7 /2018)以及匹配信息(Customer Name，Nbr，City，ExtendedNbr).
Because there is no Value of Day for 1/6/2018 or 1/7/2018, I'm needing to grab the previous day's values and input them into the newly added weekend dates (ex. 1/6/2018 and 1/7/2018) with the matching information as well (Customer Name, Nbr, City, ExtendedNbr).
在此先感谢您提供的任何帮助/建议.我仍然很新，很高兴有更多的学习机会！
Thank you in advance for any help/advice you can provide. I'm still new and any chance to learn more is greatly appreciated!
第rs.Update行错误，带有运行时错误'3022':您请求对该表进行的更改未成功，因为它们会在索引，主键或关系中创建重复的值..."
Line rs.Update errors with "Run-Time error '3022': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship..."
结果输入到所有记录的末尾.由于某种原因，循环是无限的.新日期未正确插入表格中(它们都是相同的日期，并且顺序不正确).
The results are input at the end of all the records. Loop is infinite for some reason. The new dates aren't inserted into the table properly (they're all the same dates and not in the correct order).
推荐答案
这是基于SQL的解决方案.
This is a SQL based solution.
这将获取所有记录，其中客户x存在星期五+下星期一的记录，而不是星期六的记录.
This gets all records where for customer x exists records for Friday + the following Monday but not for Saturday.
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)
，您可以使用它来插入星期六:
and you can use that to insert the Saturdays:
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.
To insert the Sundays, use the same, but replace + 1 by + 2 in both places.
要插入随机的单个失踪日(银行假期)，请将a1.ValDate + 3更改为a1.ValDate + 2，然后删除AND (DatePart("w", a1.ValDate) = 6)
To insert random single missing days (bank holidays), change a1.ValDate + 3 to a1.ValDate + 2, and remove AND (DatePart("w", a1.ValDate) = 6)
修改
如果JOIN内的DatePart()出了问题，则为备用版本:
An alternate version if DatePart() inside JOIN gives trouble:
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)
这篇关于仅使用工作日MS Access将周末插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
