Deleting particular records based on other parameters

[rgrantz]

Table 1: OrderMain
Primary Key Field Name: OrderID

Table 2: ProductDetail
Field Name: OrderID (Foreign Key relating to OrderMain Table,
many-to-one)
Field name: ItemID (can be many items per order, but each is unique)

Table 3: QualityDetail
Field Name: ItemID (Foreign Key relating to ProductDetail Table,
many-to-one)
FieldName: DefectID (can be many defects per item)


The situation is that a record of "no errors" was accidentally added to many
ItemID's that actually did have other defects. So, for instance, the
QualityDetail table may have:

ItemID DefectID
1234 10 (discoloration)
1234 24 (crack in case)
1234 13 (no defects)

I want to find all ItemID's that have more than one record in QualityDetail
AND one of the records is 13 (no defects), and if possible, automatically
delete the 13 record.

IMPORTANT: I don't just want to delete all 13's, because many ItemID's have
only the "13" record, which is actually correct. I need to delete the 13
record for ItemID's that have OTHER defect entries in ADDITION to the 13
(meaning someone entered defects, but then accidentally entered "no defects"
as well.

Is this possible? I believe I got to where I can see which ItemID's MEET
the criteria, but the recordset is not updateable. I ran a query to just
find ItemID's that had a "13" record, then a query based on THAT query that
shows all records for ItemID's matching the first query, THEN a
FindDuplicate query on the second query (thus showing all ItemID's that had
more than one Defect Record, with at least one being "13". But the
FindDuplicates is not a recordset that is editable.

Thanks for any help

 

[Brian]

Table 1: OrderMain
Primary Key Field Name: OrderID

Table 2: ProductDetail
Field Name: OrderID (Foreign Key relating to OrderMain Table,
many-to-one)
Field name: ItemID (can be many items per order, but each is unique)

Table 3: QualityDetail
Field Name: ItemID (Foreign Key relating to ProductDetail Table,
many-to-one)
FieldName: DefectID (can be many defects per item)


The situation is that a record of "no errors" was accidentally added to many
ItemID's that actually did have other defects. So, for instance, the
QualityDetail table may have:

ItemID DefectID
1234 10 (discoloration)
1234 24 (crack in case)
1234 13 (no defects)

I want to find all ItemID's that have more than one record in QualityDetail
AND one of the records is 13 (no defects), and if possible, automatically
delete the 13 record.

IMPORTANT: I don't just want to delete all 13's, because many ItemID's have
only the "13" record, which is actually correct. I need to delete the 13
record for ItemID's that have OTHER defect entries in ADDITION to the 13
(meaning someone entered defects, but then accidentally entered "no defects"
as well.

Is this possible? I believe I got to where I can see which ItemID's MEET
the criteria, but the recordset is not updateable. I ran a query to just
find ItemID's that had a "13" record, then a query based on THAT query that
shows all records for ItemID's matching the first query, THEN a
FindDuplicate query on the second query (thus showing all ItemID's that had
more than one Defect Record, with at least one being "13". But the
FindDuplicates is not a recordset that is editable.

Thanks for any help

Annoying, isn't it?

I suggest changing your working query into a make-table query, so you finish
up with a new table which contains the records you want to delete. Then, in
a new query, join the new table to the original table. You should then find
that you have an updatable query which will allow you to delete the records.

 

[Barry-Jon]

Easier way is to use a subquery as below. The subquery identifies
those items with defects other than 13. Place the subquery as a
condition on the item id In(Subquery). Then place condition 13 on the
defect id. Let me know how you get on.

DELETE tblQualityDetail.ItemID, tblQualityDetail.DefectID
FROM tblQualityDetail
WHERE (((tblQualityDetail.ItemID) In (SELECT DISTINCT
tblQualityDetail.ItemID
FROM tblQualityDetail
WHERE (((tblQualityDetail.DefectID)<>13))) AND
((tblQualityDetail.DefectID)=13));

 

[Brian]

Easier way is to use a subquery as below. The subquery identifies
those items with defects other than 13. Place the subquery as a
condition on the item id In(Subquery). Then place condition 13 on the
defect id. Let me know how you get on.

DELETE tblQualityDetail.ItemID, tblQualityDetail.DefectID
FROM tblQualityDetail
WHERE (((tblQualityDetail.ItemID) In (SELECT DISTINCT
tblQualityDetail.ItemID
FROM tblQualityDetail
WHERE (((tblQualityDetail.DefectID)<>13))) AND
((tblQualityDetail.DefectID)=13));

Yep, good idea.