delete query criteria

[angie]

i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

 

[angie]

i want to include all the data from [discount-tbl] and only the data that
match from [discount-delete-qry-00 ]

Ο χÏήστης "bhicks11 via AccessMonster.com" έγγÏαψε:

How are the Discount-tbl and discount-delete-qry-00 related?

Bonnie
http://www.dataplus-svc.com

i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

 

[John Spencer]

Do you want to permanently remove the data from Discount-tbl? That is
what a DELETE query would do.

There has to be some field (or Fields) in Discount-Delete-Qry-00 that
will identify which record(s) in Discount-tbl are associated with each
record in Discount-Delete-Qry-00.

Once you tell us that information, someone should be able to help you
build a delete query to delete the desired records.

BEFORE you run the query, I would advise you to backup the data so if
the query does not work as desired you can get back to where you started.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i want to include all the data from [discount-tbl] and only the data that
match from [discount-delete-qry-00 ]

Ο χÏήστης "bhicks11 via AccessMonster.com" έγγÏαψε:

How are the Discount-tbl and discount-delete-qry-00 related?

Bonnie
http://www.dataplus-svc.com

i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

 

[angie]

yes i want to permanently delete these records, the association is
description from discount-tbl matches Expr2 from Discount-Delete-Qry-00.

Ο χÏήστης "John Spencer" έγγÏαψε:

Do you want to permanently remove the data from Discount-tbl? That is
what a DELETE query would do.

There has to be some field (or Fields) in Discount-Delete-Qry-00 that
will identify which record(s) in Discount-tbl are associated with each
record in Discount-Delete-Qry-00.

Once you tell us that information, someone should be able to help you
build a delete query to delete the desired records.

BEFORE you run the query, I would advise you to backup the data so if
the query does not work as desired you can get back to where you started.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i want to include all the data from [discount-tbl] and only the data that
match from [discount-delete-qry-00 ]

Ο χÏήστης "bhicks11 via AccessMonster.com" έγγÏαψε:

How are the Discount-tbl and discount-delete-qry-00 related?

Bonnie
http://www.dataplus-svc.com

angie wrote:
i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

 

[angie]

hi there,

yes i am greek! i have followed your instructions and i still get the
message "define the table you want to delete data from"

here is the sql:

DELETE [DISCOUNT-TBL].DESCRIPTION, [Discount-delete-qry-00].Expr2
FROM [DISCOUNT-TBL] LEFT JOIN [Discount-delete-qry-00] ON
[DISCOUNT-TBL].DISCOUNT = [Discount-delete-qry-00].Expr2
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

can you help me fix the problem?

Ο χÏήστης "bhicks11 via AccessMonster.com" έγγÏαψε:

Hi Angie,

Ellenetha eistha?

Looks like you are missing the relationship between discount-tbl and Discount-
Delete-Qry-00. In your Query Designer, click on description in discount-tbl
and drag over to Exp2 in Discount-Delete-Qry-00 to make the relationship.
Leave your criteria as you have it. Viewing it before running to see if the
correct data set is selected (also have a back up). If you still have
problems, copy the SQL from view-sql and post it here.

Yia Sou

Bonnie

yes i want to permanently delete these records, the association is
description from discount-tbl matches Expr2 from Discount-Delete-Qry-00.

Ο χÏήστης "John Spencer" έγγÏαψε:

Do you want to permanently remove the data from Discount-tbl? That is
what a DELETE query would do.

[quoted text clipped - 37 lines]

what have i done wrong?

 

[John Spencer]

The easiest query to set up would be the following.
It should delete all record from Discount-Tbl that have no match in
discount-delete-qry-00. This is not the fastest query, so it will be
slow with a large set of records.

BEFORE trying either of these queries BACK UP your data. If they don't
work the way you want, the backup is the only way to recover.

DELETE
FROM [DISCOUNT-TBL]
WHERE [DISCOUNT-TBL].DESCRIPTION NOT IN
(SELECT [Discount-delete-qry-00].Expr2
FROM [Discount-delete-qry-00]
WHERE Expr2 is Not Null)


Faster, but more complex
DELETE
FROM [DISCOUNT-TBL]
WHERE [DISCOUNT-TBL].DESCRIPTION IN
(SELECT D.Description
FROM [Discount-Tbl] as D LEFT JOIN [Discount-delete-qry-00]
ON D.Description = [Discount-delete-qry-00].Expr2
WHERE [Discount-delete-qry-00].Expr2 is Null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

yes i want to permanently delete these records, the association is
description from discount-tbl matches Expr2 from Discount-Delete-Qry-00.

Ο χÏήστης "John Spencer" έγγÏαψε:

Do you want to permanently remove the data from Discount-tbl? That is
what a DELETE query would do.

There has to be some field (or Fields) in Discount-Delete-Qry-00 that
will identify which record(s) in Discount-tbl are associated with each
record in Discount-Delete-Qry-00.

Once you tell us that information, someone should be able to help you
build a delete query to delete the desired records.

BEFORE you run the query, I would advise you to backup the data so if
the query does not work as desired you can get back to where you started.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

i want to include all the data from [discount-tbl] and only the data that
match from [discount-delete-qry-00 ]

Ο χÏήστης "bhicks11 via AccessMonster.com" έγγÏαψε:

How are the Discount-tbl and discount-delete-qry-00 related?

Bonnie
http://www.dataplus-svc.com

angie wrote:
i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

 

[John W. Vinson]

i have entered the following sql statement:

DELETE [DISCOUNT-TBL].DESCRIPTION
FROM [DISCOUNT-TBL]
WHERE ((([Discount-delete-qry-00].Expr2) Is Null));

i want to delete only the records that are not contained in
[Discount-delete-qry-00].
i get the message pls select the table you want to delete records from.

what have i done wrong?

As suggested... *back up your database first*, then try

DELETE * FROM [DISCOUNT-TBL]
WHERE NOT EXISTS(SELECT Expr2 FROM [Discount-delete-qry-00] WHERE
[Discount-delete-qry-00].Expr2 = [DISCOUNT-TBL].[DESCRIPTION]);