CODEWITHSUNDEEP
ms-accessvba
Ed Felty
Ed Felty

Reputation: 91

Check if a column exists in a pivot query

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?

Upvotes: 3

Views: 1309

Answers (2)

Ben
Ben

Reputation: 421

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.

Upvotes: 2

HansUp
HansUp

Reputation: 97100

Set the default value to zero for any of the MyReceivingOnTimeDeliverySummary fields which may not be present in the pivot query.

Then loop through the fields in the pivot query recordset and add those fields' values to the matching fields in the other recordset.

Dim fld As DAO.Field
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do While Not rs.EOF
        rs1.AddNew
        For Each fld In rs.Fields
            rs1.Fields(fld.Name).value = rs.Fields(fld.Name).value
        Next
        rs1.Update
        rs.MoveNext
    Loop
End If

Incidentally, you may also find the code operates faster if you substitute dbAppendOnly for dbOpenDynaset here:

OpenRecordset("MyReceivingOnTimeDeliverySummary", dbOpenDynaset)

I'm unsure how much of an impact that change will have. It doesn't change the logic of what you're trying to accomplish. And perhaps any speed impact would be insignificant. But it won't cost you much to find out. :-)

Upvotes: 1

Related Questions