Delete categories in a table based on a group query

[rocketD]

Hi All,

I have a coworker who summarized his data with a group query by
category, to show the category and the count of records in each
category. He does not need records belonging to a category that has
10 or fewer records in it, so he wants to delete those records from
his table. There are several hundred categories with <10 records, so
going through the table by hand to delete the records is not feasible.

Something I did this morning, where I made a summary query and then
added it to a new query along with the table, allowed me to delete the
records. Then he decided to start over and now what I remember doing
this morning is not longer working. I'm obviously forgetting
something critical.

I tried making the following queries:

qrySummDel:
SELECT validrecords.top_pick_mrkr, Count(validrecords.top_pick_mrkr)
AS [Count]
FROM validrecords
GROUP BY validrecords.top_pick_mrkr
HAVING (((Count(validrecords.top_pick_mrkr))<10));

qryDelete:
DELETE validrecords.*
FROM validrecords INNER JOIN qrySummDel ON validrecords.top_pick_mrkr
= qrySummDel.top_pick_mrkr;

Now, in datasheet view, qryDelete shows all the records from the
validrecords table that have categories matching those selected in
qrySummDel. When I try to delete, I get this error: "Could not
delete from specified tables." I know it has something to do with the
query being linked, but I don't know how else to do this. He wants to
change his criteria, e.g., start with all the categories having <10
records, then <15 records, then <20 (because there are a few certain
categories that fit those criteria that he needs to keep, but he
doesn't know what they are without scanning the data, and there are
70,000 records.).

Can anyone give me suggestions?

 

[Jeff Boyce]

Deleting is so final... are you quite sure there'd never be a need to know
the data that had been captured in those?

You are describing a "how" -- how you/coworker wish to solve some business
need. ?!what business need?!

If you'll describe a bit more specifically what NOT having those records
will allow you to do, folks here may be able to offer alternate approaches.
For example if it's a matter that you don't want to include those records in
calculations or tabulations or reporting or ..., there may be an easy way,
via query, to EXCLUDE them, but still have the data in the table.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Try

qryDelete:
DELETE validrecords.*
FROM validrecords
WHERE Top_pick_mrkr in
(SELECT Top_Pick_Mrkr FROM qrySummDel)

As always, backup your data first. There is no way to recover your data
other than a backup if this works, but deletes the wrong records.

Now the problem is why delete these records when you could filter them
out using a similar where clause. For instance, this query should keep
all records with 10 or more records in the group.

SELECT ValidRecords.*
FROM ValidRecords
WHERE Top_Pick_Mrkr in
(SELECT Top_Pick_Mrkr
FROM ValidRecords
GROUP BY Top_pick_Mrkr
Having Count(*) > 9)

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

 

[rocketD]

Try

qryDelete:
DELETE validrecords.*
FROM validrecords
WHERE Top_pick_mrkr in
   (SELECT Top_Pick_Mrkr FROM qrySummDel)

As always, backup your data first.  There is no way to recover your data
other than a backup if this works, but deletes the wrong records.

Now the problem is why delete these records when you could filter them
out using a similar where clause.  For instance, this query should keep
all records with 10 or more records in the group.

SELECT ValidRecords.*
FROM ValidRecords
WHERE Top_Pick_Mrkr in
   (SELECT Top_Pick_Mrkr
    FROM ValidRecords
    GROUP BY Top_pick_Mrkr
    Having Count(*) > 9)

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

Hi All,

I have a coworker who summarized his data with a group query by
category, to show the category and the count of records in each
category.  He does not need records belonging to a category that has
10 or fewer records in it, so he wants to delete those records from
his table.  There are several hundred categories with <10 records, so
going through the table by hand to delete the records is not feasible.

Something I did this morning, where I made a summary query and then
added it to a new query along with the table, allowed me to delete the
records.  Then he decided to start over and now what I remember doing
this morning is not longer working.  I'm obviously forgetting
something critical.

I tried making the following queries:

qrySummDel:
SELECT validrecords.top_pick_mrkr, Count(validrecords.top_pick_mrkr)
AS [Count]
FROM validrecords
GROUP BY validrecords.top_pick_mrkr
HAVING (((Count(validrecords.top_pick_mrkr))<10));

qryDelete:
DELETE validrecords.*
FROM validrecords INNER JOIN qrySummDel ON validrecords.top_pick_mrkr
= qrySummDel.top_pick_mrkr;

Now, in datasheet view, qryDelete shows all the records from the
validrecords table that have categories matching those selected in
qrySummDel.  When I try to delete, I get this error:  "Could not
delete from specified tables."  I know it has something to do with the
query being linked, but I don't know how else to do this.  He wants to
change his criteria, e.g., start with all the categories having <10
records, then <15 records, then <20 (because there are a few certain
categories that fit those criteria that he needs to keep, but he
doesn't know what they are without scanning the data, and there are
70,000 records.).

Can anyone give me suggestions?- Hide quoted text -

- Show quoted text -

Works like a charm, thank you!!

Yes, we are sure we want to delete - basically, there is another
database that the muckety-mucks want certain info stored in, but this
dataset needs to be cleaned before importing it. Since the ultimate
destination is Oracle, and I only know a bit of Access, we have to do
the cleaning in Access. They want to eliminate the data for all the
categories that have fewer than a certain number of records and don't
meet a couple of very specific criteria, so a delete query is fine.
All of the data is backed up and stored elsewhere, but I heed your
warnings.

Thanks!