Tricky Default Values/Update Problem 1

[proximity]

Hi everyone,

Here's a nice tricky one I got landed with!

Basically, what I need to do is insert a set of default values into a table if the values don't already exist, using the WK field as the trigger! Look at this:

EmployeeName Week EmployeeNo OT2
Me Myself 27 3525215 30
Me Myself 29 3525215 15
Me Myself 30 3525215 30.5
Me Myself 32 3525215 25.5
Me Myself 33 3525215 24.5

So, Access needs to look at the week numbers and insert weeks 28 and 31 (as they are missing) with the employee name, number and a value of 36 for the OT2 field.

Any help would, as ever, be gratefully appreciated! I suspect this may involve a recordset or two . . . . ?

Thank you,

--
Steven

 

[ddiamond]

I would suggest using two insert queries - one for week 28, and the other for week 31.

insert into MyTable (EmployeeName, Week, EmployeeNo, OT)
select EmployeeName, 28 as week, EmployeeNo, 36 as OT
from MyTable a
where not exists (
  select 1 from Mytable b
  where a.EmployeeNo = b.EmployeeNo
    and b.week = 28)
group by
   EmployeeName,
   EmployeeNo;

insert into MyTable (EmployeeName, Week, EmployeeNo, OT)
select EmployeeName, 31 as week, EmployeeNo, 36 as OT
from MyTable a
where not exists (
  select 1 from Mytable b
  where a.EmployeeNo = b.EmployeeNo
    and b.week = 31)
group by
   EmployeeName,
   EmployeeNo;

 

[proximity]

Hi,

Thanks for the reply, which does indeed solve the riddle. However, and apologies for not making this clear in my original post, my table contains hundreds of employees, all with missing weeks. Obviously, the week value to insert goes from 1 to 52. I guess some sort of loop needs to be invoked?

-
Steven

 

[Golom]

Dim rs                          As Recordset
Dim EN                          As String
Dim EO                          As String
Dim LastWeek                    As Long
Dim n                           As Long

Set rs = CurrentDB.OpenRecordset( _
         "Select EmployeeName, Week, EmployeeNo, OT2 " & _
         "From myTable " & _
         "Order By 1, 2 ")

EN = ""
Do Until rs.EOF
    If EN <> rs![EmployeeName] Then
        EN = rs![EmployeeName]
        EO = rs![EmployeeNo]
        LastWeek = rs![Week]
    Else
        If LastWeek < rs![Week] - 1 Then
            For n = LastWeek + 1 To rs![Week] - 1
                CurrentDB.Execute _
                "INSERT INTO MyTable(EmployeeName,Week,EmployeeNo,OT2)" & _
                " VALUES ('" & EN & "'," & n & "," & EO & ", 36 ) "
            Next n
        End If
        LastWeek = rs![Week]
    End If
    rs.MoveNext
Loop

 

[ddiamond]

As Golom sugested you can do this with VBA. If you prefer to stick with pure SQL, you can create a table called Weeklist which has one field called Week. Populate WeekList with the values 1 through 52. Then use the following insert query:

insert into MyTable (EmployeeName, Week, EmployeeNo, OT)
select EmployeeName, WeekList.Week, EmployeeNo, 36 as OT
from MyTable a, WeekList
where not exists (
  select 1 from Mytable b 
  where a.EmployeeNo = b.EmployeeNo
    and b.week = WeekList.Week)
group by
   EmployeeName,
   EmployeeNo,
   WeekList.Week;

 

[Golom]

ddiamond

Nice code, but you need to add constraints to ensure that you don't add weeks before the first existing one or after the last. Maybe like

WHERE NOT EXISTS ...

   AND WeekList.Week BETWEEN 
          (Select MIN(b.Week) From myTable b 
           Where b.EmployeeNo = a.EmployeeNo )
       AND
          (Select MAX(b.Week) From myTable b 
           Where b.EmployeeNo = a.EmployeeNo )

 

[proximity]

Thanks everyone, especially Golom!

Your code, which I adapted to suit yet another requirement of my boss (!), was just the ticket to get me on the way. It would have taken several lifetimes and lots of lost hair to have devised the solution on my own!

Thanks again,

--
Steven