On 17 Nov 2005 00:02:35 -0800,
jimfortune@compumarc.com wrote:
[color=blue]
>paulwilliamsonremove@removespamcop.net wrote:[color=green]
>> On 16 Nov 2005 15:20:27 -0800,
jimfortune@compumarc.com wrote:
>>[color=darkred]
>> >paulwilliamsonremove@removespamcop.net wrote:
>> >> Hi,
>> >>
>> >> Using Access 2003.
>> >>
>> >> I am trying to delete records from one table/query
>> >> ("qryHistoryPersonIDs") if they don't exist in another table
>> >> ("qryDonations"). But the SQL syntax I came up with is taking forever
>> >> or locking up Access 2003.
>> >>
>> >> What am I doing wrong?
>> >>
>> >> The code is below. dFromDate_pw and dToDate_pw are set to values of
>> >> date fields on a form. qryHistoryPersonIDs contains one key field:
>> >>
>> >> DoCmd.RunSQL "Delete * From qryHistoryPersonIDs Where
>> >> qryHistoryPersonIDs.personID Not In (Select qryDonations.personid From
>> >> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> >> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>> >>
>> >> I'd rather not manually read through the table as it has 4,000 records
>> >> and is growing.
>> >>
>> >> Any ideas?
>> >>
>> >> -pw
>> >>
>> >> use paulwilliamson at spamcop dot net for e-mail
>> >
>> >The 'Not In (Select... syntax is known to be slow for larger tables.
>> >If you use the wizard to create an unmatched query it will show you an
>> >alternate syntax for returning the same records using a LEFT JOIN that
>> >runs much faster.
>> >
>> >James A. Fortune[/color]
>>
>> Hi James.
>>
>> I did as you suggested and converted this (from the wizard SQL
>> Syntax):
>>
>> SELECT tblHistoryPersonIDs.PersonID
>> FROM tblHistoryPersonIDs LEFT JOIN tblDonations ON
>> tblHistoryPersonIDs.PersonID = tblDonations.PersonID
>> WHERE (((tblDonations.PersonID) Is Null));
>>
>> To:
>>
>> DoCmd.RunSQL "Delete * From tblHistoryPersonIDs LEFT JOIN
>> tblDonations ON tblHistoryPersonIDs.PersonID = tblDonations.PersonID
>> WHERE tblDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> And I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out. Am getting sleepy ;-0
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>I didn't check your unmatched query, but when you get the unmatched
>records the 'Not In (SELECT...' switches to 'IN (SELECT
>tblHistoryPersonIDs.PersonID ...
>
>That is, the unmatched query should return the ones that are 'Not In'
>so they are now 'In.'
>
>I explained this in:
>
>
http://groups.google.com/group/comp....63454c21472dc3
>
>but I didn't do that great a job of explaining it there either. If
>that doesn't help, post back and I'll organize my thoughts into
>something a little more elegant and understandable. BTW, the ecommerce
>code in that thread was from about six years ago and I cringed when I
>saw it.
>
>James A. Fortune[/color]
I got it James:
strSQL_pw = _
"DELETE * From tblHistoryPersonIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.PersonID =
tblHistoryPersonIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"
DoCmd.RunSQL strSQL_pw
And it ran quickly when I put in a date range to delete all the
records.
Phew! That was an important stumbling block because I have to do it
for a bunch of other combinations on this form.
Thanks!
-paul
-pw
use paulwilliamson at spamcop dot net for e-mail
