Delete Query help (A2003)

**Salad** · Nov 17 '05, 07:45 PM

Re: Delete Query help (A2003)

paulwilliamsonr emove@removespa mcop.net wrote:
[color=blue]
> Hi,
>
> I have two queries: "qryHistoryPers onIDs" that just contains the
> "personID" numeric field, and "qryDonatio ns" that just contains the
> "personID" field, and a date field ("dDonationDate ").
>
> I want to delete every record in the qryHistoryPerso nIDs recordset
> that are not found in the qryDonations recordset for a particular date
> range.
>
> I have tried:
>
> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
> qryDonations Where tblDonations.[DonationDate] Not Between #" &
> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>
> But it is very slow and locks up Access.
>
> I have also tried:
>
> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
> qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
> WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
> And #" & dToDate_pw & "#"
>
> I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>
> But I am getting "Specify the table containing the records you want to
> delete". Can't figure it out.
>
> Any ideas?
>
> -pw
>
> use paulwilliamson at spamcop dot net for e-mail[/color]

Create a query. Call it QueryDateRange. Drag down the PersonID and
Date. In the criteria row for the date range enter
Between [Enter From Date] And [Enter To Date]
This will prompt you to enter a from/to date. If you know how to
program, you can adjust this to get the from/to date from a form.

Now create a net query. Call it QueryDelete. Drag the PersonId from
the history file. Add the query QueryDateRange to it. Drag down the
PersonID field. In the criteria row, enter
Is Null

Now create a relationship line between PersonID in HistoryTable and
QueryDelete. Dbl-click the relationship line and change to AllRecords
in History and only those that match (usually option2)

Now change this query to Delete from the menu.

**paulwilliamsonremove@removespamcop.net** · Nov 17 '05, 08:15 PM

Re: Delete Query help (A2003)

On Thu, 17 Nov 2005 10:29:56 -0700,
paulwilliamsonr emove@removespa mcop.net wrote:
[color=blue]
>Hi,
>
>I have two queries: "qryHistoryPers onIDs" that just contains the
>"personID" numeric field, and "qryDonatio ns" that just contains the
>"personID" field, and a date field ("dDonationDate ").
>
>I want to delete every record in the qryHistoryPerso nIDs recordset
>that are not found in the qryDonations recordset for a particular date
>range.
>
>I have tried:
>
>DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>qryHistoryPers onIDs.personID Not In (Select qryDonations.pe rsonid From
>qryDonations Where tblDonations.[DonationDate] Not Between #" &
>dFromDate_pw & "# And #" & dToDate_pw & "# )"
>
>But it is very slow and locks up Access.
>
>I have also tried:
>
>DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
>qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>And #" & dToDate_pw & "#"
>
>I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>
>But I am getting "Specify the table containing the records you want to
>delete". Can't figure it out.
>
>Any ideas?
>
>-pw
>
>use paulwilliamson at spamcop dot net for e-mail[/color]

I got it working using EXIST instead of IN:

strSQL_pw = _
"DELETE * From tblHistoryPerso nIDs " & _
"WHERE NOT EXISTS " & _
"(SELECT * " & _
"FROM tblDonations " & _
"WHERE tblDonations.Pe rsonID =
tblHistoryPerso nIDs.PersonID " & _
"AND (DonationDate < #" & dFromDate_pw & "# " & _
"OR DonationDate > #" & dToDate_pw & "#))"

DoCmd.RunSQL strSQL_pw

-pw

use paulwilliamson at spamcop dot net for e-mail

**Squirrel** · Nov 18 '05, 03:35 AM

Re: Delete Query help (A2003)

Create Query1 with this logic:

Select personid From qryDonations Where DonationDate Between #" &
dFromDate_pw & "# And #" & dToDate_pw & "# "

And then Query2 will be:
(assuming this tablename)
Delete from tblHistoryPerso nIDs where personID not in (select personid from
Query1)

HTH Linda

<paulwilliamson remove@removesp amcop.net> wrote in message
news:e2fpn1lp0k vkep454830moed6 726egu1in@4ax.c om...[color=blue]
> Hi,
>
> I have two queries: "qryHistoryPers onIDs" that just contains the
> "personID" numeric field, and "qryDonatio ns" that just contains the
> "personID" field, and a date field ("dDonationDate ").
>
> I want to delete every record in the qryHistoryPerso nIDs recordset
> that are not found in the qryDonations recordset for a particular date
> range.
>
> I have tried:
>
> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
> qryDonations Where tblDonations.[DonationDate] Not Between #" &
> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>
> But it is very slow and locks up Access.
>
> I have also tried:
>
> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
> qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
> WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
> And #" & dToDate_pw & "#"
>
> I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>
> But I am getting "Specify the table containing the records you want to
> delete". Can't figure it out.
>
> Any ideas?
>
> -pw
>
> use paulwilliamson at spamcop dot net for e-mail[/color]

**paulwilliamsonremove@removespamcop.net** · Nov 19 '05, 04:25 AM

Re: Delete Query help (A2003)

On Thu, 17 Nov 2005 19:23:26 -0800, "Squirrel" <wiseowl@covad. net>
wrote:
[color=blue]
>Create Query1 with this logic:
>
>Select personid From qryDonations Where DonationDate Between #" &
> dFromDate_pw & "# And #" & dToDate_pw & "# "
>
>And then Query2 will be:
>(assuming this tablename)
>Delete from tblHistoryPerso nIDs where personID not in (select personid from
>Query1)
>
>HTH Linda
>
>
><paulwilliamso nremove@removes pamcop.net> wrote in message
>news:e2fpn1lp0 kvkep454830moed 6726egu1in@4ax. com...[color=green]
>> Hi,
>>
>> I have two queries: "qryHistoryPers onIDs" that just contains the
>> "personID" numeric field, and "qryDonatio ns" that just contains the
>> "personID" field, and a date field ("dDonationDate ").
>>
>> I want to delete every record in the qryHistoryPerso nIDs recordset
>> that are not found in the qryDonations recordset for a particular date
>> range.
>>
>> I have tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> But it is very slow and locks up Access.
>>
>> I have also tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
>> qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>>
>> But I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out.
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>[/color]

Got a response from a Salad and a Squirrel. <g>

Thank you Linda - I will try your suggestion also!!

-pw

use paulwilliamson at spamcop dot net for e-mail

**paulwilliamsonremove@removespamcop.net** · Nov 19 '05, 04:25 AM

Re: Delete Query help (A2003)

On Thu, 17 Nov 2005 19:18:22 GMT, Salad <oil@vinegar.co m> wrote:
[color=blue]
>paulwilliamson remove@removesp amcop.net wrote:
>[color=green]
>> Hi,
>>
>> I have two queries: "qryHistoryPers onIDs" that just contains the
>> "personID" numeric field, and "qryDonatio ns" that just contains the
>> "personID" field, and a date field ("dDonationDate ").
>>
>> I want to delete every record in the qryHistoryPerso nIDs recordset
>> that are not found in the qryDonations recordset for a particular date
>> range.
>>
>> I have tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> But it is very slow and locks up Access.
>>
>> I have also tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
>> qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>>
>> But I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out.
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>
>Create a query. Call it QueryDateRange. Drag down the PersonID and
>Date. In the criteria row for the date range enter
> Between [Enter From Date] And [Enter To Date]
>This will prompt you to enter a from/to date. If you know how to
>program, you can adjust this to get the from/to date from a form.
>
>Now create a net query. Call it QueryDelete. Drag the PersonId from
>the history file. Add the query QueryDateRange to it. Drag down the
>PersonID field. In the criteria row, enter
> Is Null
>
>Now create a relationship line between PersonID in HistoryTable and
>QueryDelete. Dbl-click the relationship line and change to AllRecords
>in History and only those that match (usually option2)
>
>Now change this query to Delete from the menu.[/color]

Thank you Salad. I will give it a got.

-pw

use paulwilliamson at spamcop dot net for e-mail

**paulwilliamsonremove@removespamcop.net** · Nov 19 '05, 05:25 AM

Re: Delete Query help (A2003)

On Thu, 17 Nov 2005 19:23:26 -0800, "Squirrel" <wiseowl@covad. net>
wrote:
[color=blue]
>Create Query1 with this logic:
>
>Select personid From qryDonations Where DonationDate Between #" &
> dFromDate_pw & "# And #" & dToDate_pw & "# "
>
>And then Query2 will be:
>(assuming this tablename)
>Delete from tblHistoryPerso nIDs where personID not in (select personid from
>Query1)
>
>HTH Linda
>[/color]

Makes way too much sense Linda! I was looking for a much harder way
to do this <BG>

Thank you Squirrel!

Don't have any of those around here in Montana, besides a pine
squirrel once in a while :-)

[color=blue]
>
><paulwilliamso nremove@removes pamcop.net> wrote in message
>news:e2fpn1lp0 kvkep454830moed 6726egu1in@4ax. com...[color=green]
>> Hi,
>>
>> I have two queries: "qryHistoryPers onIDs" that just contains the
>> "personID" numeric field, and "qryDonatio ns" that just contains the
>> "personID" field, and a date field ("dDonationDate ").
>>
>> I want to delete every record in the qryHistoryPerso nIDs recordset
>> that are not found in the qryDonations recordset for a particular date
>> range.
>>
>> I have tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs Where
>> qryHistoryPerso nIDs.personID Not In (Select qryDonations.pe rsonid From
>> qryDonations Where tblDonations.[DonationDate] Not Between #" &
>> dFromDate_pw & "# And #" & dToDate_pw & "# )"
>>
>> But it is very slow and locks up Access.
>>
>> I have also tried:
>>
>> DoCmd.RunSQL "Delete * From qryHistoryPerso nIDs LEFT JOIN
>> qryDonations ON qryHistoryPerso nIDs.PersonID = tblDonations.Pe rsonID
>> WHERE qryDonations.[DonationDate] Not Between #" & dFromDate_pw & "#
>> And #" & dToDate_pw & "#"
>>
>> I populate dFromDate_pw and dToDate_pw with dates entered on a form.
>>
>> But I am getting "Specify the table containing the records you want to
>> delete". Can't figure it out.
>>
>> Any ideas?
>>
>> -pw
>>
>> use paulwilliamson at spamcop dot net for e-mail[/color]
>[/color]
-pw

use paulwilliamson at spamcop dot net for e-mail

Delete Query help (A2003)

Delete Query help (A2003)

Comment

Comment

Comment

Comment

Comment

Comment