Deleting records

[Guest]

This table I am working on contains a large number of records containing data
about students. The first 2 fields identify the student uniquely; the third
field contains one bit of the data. There are about 9 records for a student,
each containing different kinds of data in the third field. The records for
a given student are consecutive in the table. (I know it sounds like a
rather strange table, but this is where I am right now!)

I now need to update the database so that it contains only the first 6
records for each student. So, the query will idendify the first 6 records
for each student and then delete any other records it finds with the same
student identifier. Is there some way of doing this?

Alternatively, perhaps I could do the sensible thing and combine each bit of
data into a single record for each student. Then the first 2 fields would be
the identifier followed by a series of fields each containing one bit of
data. I could then keep the first 6 data fields and delete the rest.

I hope this makes sense - any help would be much appreciated.

Jim Jones
Botswana

 

[Guest]

Jim,

I can see two ways to do what you want here, but both are a little
complicated.
Which way you can go hinges on this question: can you *100% guarantee* that
ONLY the records you want to keep for all the students will show up in a
summary SELECT TOP 6 query? If so, then you can build that query, (call it
QryA) and then create a DELETE Query (QryB) where you link the table to qryA
(to itself, basically) with outer joins to qryA (including all records from
the table) and then you can select only those records from the table where
the linked fields are NULL.
This will delete all the records in the table that don't match those in its
TOP 6 summary query.

That said, personal experience makes me highly doubt that a simple TOP 6
summary query will give you that 100% certainty that you get only (and *ALL*)
the data you really want. So, I'd most likely be writing a VBA function to
intelligently select and delete the extraneous data records (using LIKE and
NOT LIKE expressions to qualify/disqualify the records for deletion depending
entirely upon your requirements and what's really in all the data.

 

[Guest]

Mark

Thanks for the response. I can confirm that, in this table, I need to keep
only the top first 6 records for each student. So, could you give me a bit
more detail on how I can use the SELECT TOP 6 query which you suggested.

Many thanks again.

Jim Jones
Botswana

 

[Guest]

Jim,

OK, I set up a sample table:
ID Category Item Data
1 A AA Yadda1
2 A AA Yadda2
3 A AA Yadda3
4 A AA Yadda4
5 A AA Yadda5
6 A AB Yadda1
7 A AB Yadda2
8 A AB Yadda3
9 A AB Yadda4
10 A AB Yadda5
11 A BB Yadda1
12 A BB Yadda2
13 A BB Yadda5
14 A BB Yadda6
15 B BA Boingo1
16 B BA Boingo2
17 B BA Biongo3
18 B BA Boingo4
19 B BA Boingo5
20 B BA Boingo6

Assuming that your request is really translated to: "I need to delete the
records for each Item in each Category that are not in the top n Data items
(sorted Ascending)."
....we'll use the top 3 items for an example instead of your 6.
I was wrong abut this being something easy enough to do without writing VBA
code.
This needs to be coded.

But first we need the queries:
Query1: a query to select the distinct Items(and Categories):
SELECT DISTINCT Category, Item FROM TestTable;

Query2: a query (which must be built on-the-fly - and this is why you need
to do this in code) to select the top 3 items for each item
[inside a loop through the Query1 data records]
SELECT TOP 3 ID, Data FROM TestTable
WHERE (TestTable.Category="A") AND (TestTable.Item="AB");

Query3: the actual DELETE query (which must also be built on-the-fly in code)
DELETE TestTable.* FROM TestTable LEFT JOIN Query2 ON TestTable.ID = Query2.ID
WHERE (Query2.ID Is Null) AND (TestTable.Category="A") AND
(TestTable.Item="AB");

Executing query 3 will yield the folowing data (for Category="A", Item="AB")
ID Category Item Data
6 A AB Yadda1 (not deleted)
7 A AB Yadda2 (not deleted)
8 A AB Yadda3 (not deleted)
9 A AB Yadda4 (Deleted Record)
10 A AB Yadda5 (Deleted Record)

As you loop through the Query1 records in code, just Create(or modify the
..SQL if it exists already) and save the Query2 Querydef and then Create and
execute the Query3 SQL (the SQL WHERE clauses are what need to be modified
on-the-fly)
and move through the Query1 loop to the end.

Give another yell back if you need help with the coding of this.