[AccessD] Optimizing subqueries

Actually, to determine if a key is not in another table, do a left or
right join, and check for null:

tblOne
OrgID

tblTwo
OrgID

SELETE tblOne.OrgID
FROM tblOne LEFT JOIN tblTwo ON tblOne.OrgID=tblTwo.OrgID
WHERE tblTwo.OrgID Is Null

The above SQL will show you every OrgID that's in table One, but NOT in
table two.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Max Wanadoo
Sent: Tuesday, June 03, 2008 12:45 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Optimizing subqueries

No not really because of the fact that the OrgID must NOT be in two of
the
tables.  Ie, it is a "Not in (Select * etc)" clause.

Max
 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, June 03, 2008 6:34 PM
To: accessd at databaseadvisors.com
Subject: Re: [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

-- 


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.