Rusty DBA needs some help :)

[aggiekim]

Hi all,



I used to be a high-powered Oracle & SQL Server DBA for a large oil company, but I've been raising a family for the last 7 years. I love offering my talents to my kids' school district though, and that's just what I've been struggling with today.

Say I've got a table with four columns - ID, DATE SCHEDULE_A and SCHEDULE_B. The schedule rotates every four school days, like so:

ID DATE SCHEDULE_A SCHEDULE_B
1 9/8/14 1 1a
2 9/9/14 2 2a
3 9/10/14 3 3a
4 9/11/14 4 4a
5 9/12/14 1 1b
6 9/15/14 2 2b

...and on until the end of the school year.

My dilemma is this: When a snow day occurs, the schedule needs to be bumped - which is VERY easy with an Excel spreadsheet, but for some reason I'm drawing a blank here with Access. I've come up with a solution that updates the SCHEDULE_A and SCHEDULE_B rows with the previous ID's data, and tried this with the duplicate table concept (among many others) to no avail:

UPDATE test INNER JOIN Test AS Test_1 ON test.ID = Test_1.ID SET test.lib_cd = test_1.lib_cd
WHERE (((test.id)=([test_1].[id]-1))) and test.cd = date();

I also just tried to update the table with data from itself using the same idea of subtracting 1 from the ID (I can't subtract 1 day, because then we get into the problem of no school on weekends if the "snow day" happens on a Monday)

Assuming a "snow day" on 9/10/14, my data ultimately needs to look like this:

ID DATE SCHEDULE_A SCHEDULE_B
1 9/8/14 1 1a
2 9/9/14 2 2a
3 9/10/14 3 3a
4 9/11/14 3 3a
5 9/12/14 4 4a
6 9/15/14 1 1a

What am I doing wrong here?

Thank you!!

[ranman256]

You could scan thru the records and assign it to the one before...
'run this code from a macro: RUNCODE BumpDates("9/1/2014" )

publick Function BumpDates(byval pvSnowDay as date)
Dim sSql As String
Dim rst  'As Recordset
dim A, B, vNextA, vNextB
sSql = "select * from TABLE sort by [date] ascending where [date] >= #" & pvSnowDay & "# "
Set rst = CurrentDb.OpenRecordset(sSql)
With rst
   while not .eof()
     if .Fields("Date").Value  = pvsnowday then
         A = .Fields("SCHEDULE_A").Value & ""
         B = .Fields("SCHEDULE_B").Value & ""
      else
   
            'get the old values
         vNextA = .Fields("SCHEDULE_A").Value & ""
         vNextB = .Fields("SCHEDULE_B").Value & ""
            'reset the day 
      .edit
        .Fields("SCHEDULE_A").Value = vA
        .Fields("SCHEDULE_B").Value = vB
      .update
    endif 
     vA = vNextA
     vB = vNextB
     .MoveNext
   wend
End With
rst.Close
Set rst = Nothing
End Function

[ssanfu]

Here is my version....

I used a table name of "Schedules" and field names
SchoolID (Autonumber - PK)
SchoolDate (Date/Time) (Date is a reserved word in Access)
Schedule_A (Text)
Schedule_B (Text)

Public Function UpdateSchedulesForSnow(ByVal pvSnowDay As Date)
    Dim r As DAO.Recordset
    
    Dim sSql As String
    Dim CurA As String
    Dim CurB As String
    Dim OldA As String
    Dim OldB As String

    sSql = "SELECT *"
    sSql = sSql & " FROM Schedules"
    sSql = sSql & " WHERE (((Schedules.[Schooldate]) >= #" & pvSnowDay & "#))"
    sSql = sSql & " ORDER BY Schedules.[Schooldate];"

    Set r = CurrentDb.OpenRecordset(sSql)

    With r
        Do While Not .EOF()
            'Snow day schedules
            If !SchoolDate = pvSnowDay Then
                CurA = !SCHEDULE_A
                CurB = !SCHEDULE_B
            Else

                'Save the current values
                OldA = !SCHEDULE_A
                OldB = !SCHEDULE_B
                
                'update schedule
                .Edit
                !SCHEDULE_A = CurA
                !SCHEDULE_B = CurB
                .Update
                
                'save old to current
                CurA = OldA
                CurB = OldB
                
                OldA = ""
                OldB = ""
            End If

            .MoveNext
        Loop
    End With
    
    r.Close
    Set r = Nothing
    
    MsgBox "Done"
End Function

I tested using the data example. It iwas a small sample, but it seems to work...


And welcome to the forum

[aggiekim]

Thanks to everyone for taking the time to respond, I'll give these a try.

Can anyone explain why my original thought process won't work? I'd like to know where I've gone wrong with my logic. Thanks!