Hi,
I need to execute a join many times, and each time, add the results to a third table. If a record already exists in the third table, instead of adding the row, I need to add the value of one of it's fields to a 'count' field in the original table.
The join isn't a problem. It's the update. I'm trying to remove client-side logic that uses DAO to compare each record in the join recordset, like:
.Seek "=", rs!ZIP, rs!STNAME, rs!CNTYNAME
If .NoMatch Then
.AddNew
!ZIP_CODE = rs!ZIP
!State = rs!STNAME
!COUNTY = rs!CNTYNAME
!REG_COUNT = rs!Count
.Update
Else
.Edit
!REG_COUNT = !REG_COUNT + rs!Count
.Update
End If
I figure I can do the join, append the results to the new table, and run a GROUP BY on ZIP, STNAME, CNTYNAME. I have to join 100 times, so it seems wasteful to execute the group by each time, but the table would become too large if I just appended all the results of the joins, and then ran a group by. Any suggestions?
I need to execute a join many times, and each time, add the results to a third table. If a record already exists in the third table, instead of adding the row, I need to add the value of one of it's fields to a 'count' field in the original table.
The join isn't a problem. It's the update. I'm trying to remove client-side logic that uses DAO to compare each record in the join recordset, like:
.Seek "=", rs!ZIP, rs!STNAME, rs!CNTYNAME
If .NoMatch Then
.AddNew
!ZIP_CODE = rs!ZIP
!State = rs!STNAME
!COUNTY = rs!CNTYNAME
!REG_COUNT = rs!Count
.Update
Else
.Edit
!REG_COUNT = !REG_COUNT + rs!Count
.Update
End If
I figure I can do the join, append the results to the new table, and run a GROUP BY on ZIP, STNAME, CNTYNAME. I have to join 100 times, so it seems wasteful to execute the group by each time, but the table would become too large if I just appended all the results of the joins, and then ran a group by. Any suggestions?