By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,962 Members | 1,210 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,962 IT Pros & Developers. It's quick & easy.

Delete Query help (A2003)

P: n/a
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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
Nov 17 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
pa******************@removespamcop.net wrote:
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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


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.

Nov 17 '05 #2

P: n/a
On Thu, 17 Nov 2005 10:29:56 -0700,
pa******************@removespamcop.net wrote:
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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


I got it working using EXIST instead of IN:
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

-pw

use paulwilliamson at spamcop dot net for e-mail
Nov 17 '05 #3

P: n/a
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 tblHistoryPersonIDs where personID not in (select personid from
Query1)

HTH Linda
<pa******************@removespamcop.net> wrote in message
news:e2********************************@4ax.com...
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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

Nov 18 '05 #4

P: n/a
On Thu, 17 Nov 2005 19:23:26 -0800, "Squirrel" <wi*****@covad.net>
wrote:
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 tblHistoryPersonIDs where personID not in (select personid from
Query1)

HTH Linda
<pa******************@removespamcop.net> wrote in message
news:e2********************************@4ax.com.. .
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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


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
Nov 19 '05 #5

P: n/a
On Thu, 17 Nov 2005 19:18:22 GMT, Salad <oi*@vinegar.com> wrote:
pa******************@removespamcop.net wrote:
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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


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.


Thank you Salad. I will give it a got.

-pw

use paulwilliamson at spamcop dot net for e-mail
Nov 19 '05 #6

P: n/a
On Thu, 17 Nov 2005 19:23:26 -0800, "Squirrel" <wi*****@covad.net>
wrote:
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 tblHistoryPersonIDs where personID not in (select personid from
Query1)

HTH Linda

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 :-)


<pa******************@removespamcop.net> wrote in message
news:e2********************************@4ax.com.. .
Hi,

I have two queries: "qryHistoryPersonIDs" that just contains the
"personID" numeric field, and "qryDonations" that just contains the
"personID" field, and a date field ("dDonationDate").

I want to delete every record in the qryHistoryPersonIDs recordset
that are not found in the qryDonations recordset for a particular date
range.

I have tried:

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 & "# )"

But it is very slow and locks up Access.

I have also tried:

DoCmd.RunSQL "Delete * From qryHistoryPersonIDs LEFT JOIN
qryDonations ON qryHistoryPersonIDs.PersonID = tblDonations.PersonID
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

-pw

use paulwilliamson at spamcop dot net for e-mail
Nov 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.