Inserting Weekends into Table with only Weekdays MS Access

Question

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.

Code so far:

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

Data that I'm needing:

+------------------------------------------------------------------------+
|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

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!

EDIT1:

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..."

EDIT2:

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).

Andre · Accepted Answer

This is a SQL based solution.

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)

To insert the Sundays, use the same, but replace + 1 by + 2 in both places.

To insert random single missing days (bank holidays), change a1.ValDate + 3 to a1.ValDate + 2, and remove AND (DatePart("w", a1.ValDate) = 6)

Edit

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)

Inserting Weekends into Table with only Weekdays MS Access

Answers (2)

When entering dates as query criteria, enclose them in pound signs (#)

Related Questions