Check if a column exists in a pivot query

Question

I have a pivot query I need to loop through and add to another temporary table. The pivot query is a sum of the different statuses found. The statuses are Early, Late, and On-Time. Based on what the user selects, not all of the statuses are present. So when I run the following:

Set rs1 = CurrentDb.OpenRecordset("MyReceivingOnTimeDeliverySummary", dbOpenDynaset)
Set rs = CurrentDb.OpenRecordset("TRANSFORM Sum(recvqty) AS SumOfrecvqty " & _
                                 "SELECT supname, Sum(recvqty) AS TotalReceivedQty " & _
                                 "FROM MyReceivingOnTimeDeliveryDetail " & _
                                 "GROUP BY supname " & _
                                 "PIVOT Status", dbOpenDynaset)
If (rs.RecordCount <> 0) Then
   rs.MoveFirst
   Do While rs.EOF <> True
      rs1.AddNew
      rs1.Fields("[supname]").value = rs.Fields("[supname]").value
      rs1.Fields("[TotalReceivedQty]").value = rs.Fields("[TotalReceivedQty]").value
      rs1.Fields("[Early]").value = rs.Fields("[Early]").value
      rs1.Fields("[Late]").value = rs.Fields("[Late]").value
      rs1.Fields("[OnTime]").value = rs.Fields("[On-Time]").value
      rs1.Update
      rs.MoveNext
   Loop
End If

If one of the statuses isn't in the results of the query then I will get an error where I am adding that value to the MyReceivingOnTimeDeliverySummary table.

How do I test to for each status and if they are not there then add as 0?

Answer 1

You should be avoiding recordsets for simple operations, like copying with small, uniform changes, in this case. But good news: this makes everything easier!

First, use the SQL statement you already have to create a query.

Dim db As Database
Set db= CurrentDb
db.CreateQueryDef "qry1", "sqltext"

Then, from that query, SELECT INTO (or INSERT INTO) your summary table.

db.Execute "SELECT * INTO MyReceivingOnTimeDeliverySummary FROM qry1"

Then you can add the fields if they aren't there.

On Error Resume Next: db.Execute "ALTER TABLE MyReceivingOnTimeDeliverySummary ADD COLUMN Early NUMBER": Err.Clear: On Error GoTo 0
On Error Resume Next: db.Execute "ALTER TABLE MyReceivingOnTimeDeliverySummary ADD COLUMN Late NUMBER": Err.Clear: On Error GoTo 0
On Error Resume Next: db.Execute "ALTER TABLE MyReceivingOnTimeDeliverySummary ADD COLUMN OnTime NUMBER": Err.Clear: On Error GoTo 0

Finally, fix the nulls to zero.

db.Execute "UPDATE [MyReceivingOnTimeDeliverySummary] SET [Early] = Nz([Early],0)"
db.Execute "UPDATE [MyReceivingOnTimeDeliverySummary] SET [Late] = Nz([Late],0)"
db.Execute "UPDATE [MyReceivingOnTimeDeliverySummary] SET [OnTime] = Nz([OnTime],0)"

Why do it this way? In my experience, SQL is a lot faster than recordsets.