[AccessD] Optimizing subqueries

Hi Max

On my way home .. but couldn't you just pull in the tables you lookup and create inner joins on the three

  tablename.FKOrgID=mcmOrganisations.OrgID

?

/gustav

>>> max.wanadoo at gmail.com 03-06-2008 19:22 >>>
Gustav,

I need to try to optimize this function into a query (or set of queries).
Can you help?
When I try to do it, the "NOT IN" clause is so slllloooowww that I resorted
to creating this temp table.

Max

Public Function pfpf()
  Dim datStart As Date, datEnd As Date
  Dim sql As String, rst As DAO.Recordset, rst2 As DAO.Recordset, lngRecCt
As Long
  datStart = Time()
  Debug.Print datStart,
  
  sql = "Drop Table tblTempOrgs"
  On Error Resume Next
  CurrentDb.Execute (sql)
  sql = "Create Table tblTempOrgs (FKOrgID Long)"
  CurrentDb.Execute (sql)
  sql = "Create Unique Index FKOrgID on tblTempOrgs (FKOrgID)"
  CurrentDb.Execute (sql)
  sql = "Select OrgID, FKOrgTypeID from mcmOrganisations Where
FKDefaultROID=11200 and (FKOrgTypeID = 1 or FKOrgTypeID = 4 or (FKOrgTypeID
> 9 and FKOrgTypeID < 13))"
  Set rst = CurrentDb.OpenRecordset(sql)
  rst.MoveFirst
  Do While Not rst.EOF
    If DCount("*", "LinkTrusts2Contacts", "FKOrgID=" & rst!OrgID) > 0 Then '
there is a contact
      If DCount("*", "mcmIncomeTransactions", "FKOrgID=" & rst!OrgID) = 0
Then ' no previous transaction
        If DCount("*", "tblTrustBids", "FKOrgID=" & rst!OrgID) = 0 Then 'no
previous bids
          sql = "Insert into tblTempOrgs (FKOrgID) values (" & rst!OrgID &
")"
          CurrentDb.Execute (sql)
        End If
      End If
    End If
    rst.MoveNext
  Loop
  lngRecCt = DCount("*", "tblTempOrgs")
  datEnd = Time()
  Debug.Print datEnd, DateDiff("s", datStart, datEnd), lngRecCt
  Debug.Print "==============================================="
End Function

--