Return Bottom Values

[tkosel]

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?

 

[Danny Lesandrini]

Reverse the sort order.

 

[tkosel]

Then what? I don't want to manaully delete these records, I want to automate
it. I don't think the sort order is going to help me select records to
delete automatically. (I could do it manually no matter what order it was
sorted!)

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?

 

[Danny Lesandrini]

Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Then what? I don't want to manaully delete these records, I want to automate
it. I don't think the sort order is going to help me select records to
delete automatically. (I could do it manually no matter what order it was
sorted!)

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?

 

[BruceM]

I think the problem here is that the user wants to delete everything except
the top 10. If there are 25 records, deleting the bottom 10 will leave 15.
Same idea applies to viewing everything except the top 10. In VBA I would
try getting the record count minus 10, then use that result for the TOP
number, with the records sorted DESC, but I do not see how to do that
directly in a query. I expect it can be done; I just don't know how.

To the OP, why delete the records? Access can handle millions of properly
indexed records. Why not just view the top 10?

Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Then what? I don't want to manaully delete these records, I want to
automate
it. I don't think the sort order is going to help me select records to
delete automatically. (I could do it manually no matter what order it
was
sorted!)

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to
return
bottom values.

I have a table with a primary key called [Key]. It contains unique
values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234,
10-10C-1234
etc. The letter in each string represents a Rev (Revision) level.
There may
be many revs. I want to write a query that deletes all but the
latest rev.
I know how to return the MAX value which returns the values
01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to
keep, the
rest need to be deleted. I cannot think of a good way to delete all
but the
max value. Any ideas?

 

[Ken Sheridan]

Try this:

DELETE *
FROM YourTable AS T1
WHERE MID(Key,6,1) <>
(SELECT MAX(MID(Key,6,1))
FROM YourTable AS T2
WHERE LEFT(T2.Key,5) = LEFT(T1.Key,5)
AND RIGHT(T2.Key,5) = RIGHT(T1.Key,5));

The way it works is that the subquery is correlated with the outer query on
the five left and right characters of the key, so the subquery returns the
row with the maximum value at the 6th position in the string for those rows.
The outer query is restricted to those rows which don't match this value, so
all but the rows which do are deleted.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

It’s made a little more complex as its all but the top 1 per subset of rows
(as defined by the key value excluding the revision value) which are to be
deleted. As you'll see from my reply this can be done by means of a subquery
which restricts the outer query. In this case as it’s the top 1 per group
which are to remain the MAX operator can be used. If it were a constant
number of rows greater than 1 per group to be retained then the TOP option
could be used in the subquery, which again would be correlated with the outer
query, and sorted in DESC order. In this case a NOT IN operation would be
undertaken on the subquery rather than the inequality operation in my reply.
If portability is an issue it could also be done without having to resort to
the non-standard TOP option by using the COUNT operator to identify the
relevant rows to be retained, i.e. delete those where the count of rows
returned by a subquery correlated on the unrevised key values being the same
and the revision value being <= that in the outer query's current row is more
than 1.

Ken Sheridan
Stafford, England

I think the problem here is that the user wants to delete everything except
the top 10. If there are 25 records, deleting the bottom 10 will leave 15.
Same idea applies to viewing everything except the top 10. In VBA I would
try getting the record count minus 10, then use that result for the TOP
number, with the records sorted DESC, but I do not see how to do that
directly in a query. I expect it can be done; I just don't know how.

To the OP, why delete the records? Access can handle millions of properly
indexed records. Why not just view the top 10?

Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com

Then what? I don't want to manaully delete these records, I want to
automate
it. I don't think the sort order is going to help me select records to
delete automatically. (I could do it manually no matter what order it
was
sorted!)


:

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to
return
bottom values.

I have a table with a primary key called [Key]. It contains unique
values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234,
10-10C-1234
etc. The letter in each string represents a Rev (Revision) level.
There may
be many revs. I want to write a query that deletes all but the
latest rev.
I know how to return the MAX value which returns the values
01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to
keep, the
rest need to be deleted. I cannot think of a good way to delete all
but the
max value. Any ideas?

 

[BruceM]

You answered the questions I was about to ask. It's clear enough now that
you mention it that TOP 10 can take the place of MAX. I see what you are
saying about NOT IN rather than <>.

I have to admit I have no real idea what you are talking about with COUNT.
After at least 30 minutes of trying I cannot get SELECT COUNT to do anything
useful. For instance:
SELECT Count([City] WHERE Left([City],1) = "A") FROM tblVendor;
tells me only that there is a missing operator, but I just don't see it. Of
course, if I cannot get a count of cities beginning with the letter "A" I
will be quite unable to do anything as sophisticated as identifying the top
10 records or whatever. I have searched Help, but as usual it gives just
enough information to suggest something is possible without actually
providing enound information to accomplish it.

It’s made a little more complex as its all but the top 1 per subset of
rows
(as defined by the key value excluding the revision value) which are to be
deleted. As you'll see from my reply this can be done by means of a
subquery
which restricts the outer query. In this case as it’s the top 1 per group
which are to remain the MAX operator can be used. If it were a constant
number of rows greater than 1 per group to be retained then the TOP option
could be used in the subquery, which again would be correlated with the
outer
query, and sorted in DESC order. In this case a NOT IN operation would be
undertaken on the subquery rather than the inequality operation in my
reply.
If portability is an issue it could also be done without having to resort
to
the non-standard TOP option by using the COUNT operator to identify the
relevant rows to be retained, i.e. delete those where the count of rows
returned by a subquery correlated on the unrevised key values being the
same
and the revision value being <= that in the outer query's current row is
more
than 1.

Ken Sheridan
Stafford, England

I think the problem here is that the user wants to delete everything
except
the top 10. If there are 25 records, deleting the bottom 10 will leave
15.
Same idea applies to viewing everything except the top 10. In VBA I
would
try getting the record count minus 10, then use that result for the TOP
number, with the records sorted DESC, but I do not see how to do that
directly in a query. I expect it can be done; I just don't know how.

To the OP, why delete the records? Access can handle millions of
properly
indexed records. Why not just view the top 10?

Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Then what? I don't want to manaully delete these records, I want to
automate
it. I don't think the sort order is going to help me select records
to
delete automatically. (I could do it manually no matter what order it
was
sorted!)


:

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



I read several posts relative to returning top N values. Perhaps I
am
stupid, but I cannot make the transition to apply similar logic to
return
bottom values.

I have a table with a primary key called [Key]. It contains unique
values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234,
10-10C-1234
etc. The letter in each string represents a Rev (Revision) level.
There may
be many revs. I want to write a query that deletes all but the
latest rev.
I know how to return the MAX value which returns the values
01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to
keep, the
rest need to be deleted. I cannot think of a good way to delete
all
but the
max value. Any ideas?

 

[Ken Sheridan]

Using Northwind as an example there are several ways it could be done. Say
we want to return all but the three earliest orders by each customer:

SELECT *
FROM ORDERS AS O1
WHERE EXISTS
(SELECT CustomerID
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND O2.OrderDate <= O1.OrderDate
GROUP BY CustomerID
HAVING COUNT(*) > 3);

or:

SELECT *
FROM Orders AS O1
WHERE
(SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND O2.OrderDate <= O1.OrderDate) > 3;

The first is probably the most efficient.

Ken Sheridan
Stafford, England

You answered the questions I was about to ask. It's clear enough now that
you mention it that TOP 10 can take the place of MAX. I see what you are
saying about NOT IN rather than <>.

I have to admit I have no real idea what you are talking about with COUNT.
After at least 30 minutes of trying I cannot get SELECT COUNT to do anything
useful. For instance:
SELECT Count([City] WHERE Left([City],1) = "A") FROM tblVendor;
tells me only that there is a missing operator, but I just don't see it. Of
course, if I cannot get a count of cities beginning with the letter "A" I
will be quite unable to do anything as sophisticated as identifying the top
10 records or whatever. I have searched Help, but as usual it gives just
enough information to suggest something is possible without actually
providing enound information to accomplish it.

It’s made a little more complex as its all but the top 1 per subset of
rows
(as defined by the key value excluding the revision value) which are to be
deleted. As you'll see from my reply this can be done by means of a
subquery
which restricts the outer query. In this case as it’s the top 1 per group
which are to remain the MAX operator can be used. If it were a constant
number of rows greater than 1 per group to be retained then the TOP option
could be used in the subquery, which again would be correlated with the
outer
query, and sorted in DESC order. In this case a NOT IN operation would be
undertaken on the subquery rather than the inequality operation in my
reply.
If portability is an issue it could also be done without having to resort
to
the non-standard TOP option by using the COUNT operator to identify the
relevant rows to be retained, i.e. delete those where the count of rows
returned by a subquery correlated on the unrevised key values being the
same
and the revision value being <= that in the outer query's current row is
more
than 1.

Ken Sheridan
Stafford, England

I think the problem here is that the user wants to delete everything
except
the top 10. If there are 25 records, deleting the bottom 10 will leave
15.
Same idea applies to viewing everything except the top 10. In VBA I
would
try getting the record count minus 10, then use that result for the TOP
number, with the records sorted DESC, but I do not see how to do that
directly in a query. I expect it can be done; I just don't know how.

To the OP, why delete the records? Access can handle millions of
properly
indexed records. Why not just view the top 10?


Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Then what? I don't want to manaully delete these records, I want to
automate
it. I don't think the sort order is going to help me select records
to
delete automatically. (I could do it manually no matter what order it
was
sorted!)


:

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



I read several posts relative to returning top N values. Perhaps I
am
stupid, but I cannot make the transition to apply similar logic to
return
bottom values.

I have a table with a primary key called [Key]. It contains unique
values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234,
10-10C-1234
etc. The letter in each string represents a Rev (Revision) level.
There may
be many revs. I want to write a query that deletes all but the
latest rev.
I know how to return the MAX value which returns the values
01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to
keep, the
rest need to be deleted. I cannot think of a good way to delete
all
but the
max value. Any ideas?

 

[BruceM]

OK, it looks like I had the Count in the wrong place or with the wrong
syntax or whatever. I thought I was following the example in the Help file,
but I guess that only works in a very narrow application.

Thanks for the reply. I'll take it up again at my next opportunity, which
will either be tomorrow or some time next week.

Using Northwind as an example there are several ways it could be done.
Say
we want to return all but the three earliest orders by each customer:

SELECT *
FROM ORDERS AS O1
WHERE EXISTS
(SELECT CustomerID
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND O2.OrderDate <= O1.OrderDate
GROUP BY CustomerID
HAVING COUNT(*) > 3);

or:

SELECT *
FROM Orders AS O1
WHERE
(SELECT COUNT(*)
FROM Orders AS O2
WHERE O2.CustomerID = O1.CustomerID
AND O2.OrderDate <= O1.OrderDate) > 3;

The first is probably the most efficient.

Ken Sheridan
Stafford, England

You answered the questions I was about to ask. It's clear enough now
that
you mention it that TOP 10 can take the place of MAX. I see what you are
saying about NOT IN rather than <>.

I have to admit I have no real idea what you are talking about with
COUNT.
After at least 30 minutes of trying I cannot get SELECT COUNT to do
anything
useful. For instance:
SELECT Count([City] WHERE Left([City],1) = "A") FROM tblVendor;
tells me only that there is a missing operator, but I just don't see it.
Of
course, if I cannot get a count of cities beginning with the letter "A" I
will be quite unable to do anything as sophisticated as identifying the
top
10 records or whatever. I have searched Help, but as usual it gives just
enough information to suggest something is possible without actually
providing enound information to accomplish it.

It’s made a little more complex as its all but the top 1 per subset of
rows
(as defined by the key value excluding the revision value) which are to
be
deleted. As you'll see from my reply this can be done by means of a
subquery
which restricts the outer query. In this case as it’s the top 1 per
group
which are to remain the MAX operator can be used. If it were a
constant
number of rows greater than 1 per group to be retained then the TOP
option
could be used in the subquery, which again would be correlated with the
outer
query, and sorted in DESC order. In this case a NOT IN operation would
be
undertaken on the subquery rather than the inequality operation in my
reply.
If portability is an issue it could also be done without having to
resort
to
the non-standard TOP option by using the COUNT operator to identify the
relevant rows to be retained, i.e. delete those where the count of rows
returned by a subquery correlated on the unrevised key values being the
same
and the revision value being <= that in the outer query's current row
is
more
than 1.

Ken Sheridan
Stafford, England

:

I think the problem here is that the user wants to delete everything
except
the top 10. If there are 25 records, deleting the bottom 10 will
leave
15.
Same idea applies to viewing everything except the top 10. In VBA I
would
try getting the record count minus 10, then use that result for the
TOP
number, with the records sorted DESC, but I do not see how to do that
directly in a query. I expect it can be done; I just don't know how.

To the OP, why delete the records? Access can handle millions of
properly
indexed records. Why not just view the top 10?


Ok, this is just a conceptual thing. Let's say you want to see all
sales rep statistics for the month of January, 2009. The query
would look like this

SELECT RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal ASC

What if you wanted to see the the 10 HIGHEST sales results?
Add TOP 10 to the select and sort Descending.

SELECT TOP 10 RepName, SalesTotal
FROM qryOrders
WHERE OrderDate BETWEEN #1/1/09# AND #1/31/09#
ORDER BY SalesTotal DESC

How would you show the 10 LOWEST sales results? Same
query, same TOP 10, but sort on Sales Totals Ascending.

Clear as mud?
--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



Then what? I don't want to manaully delete these records, I want
to
automate
it. I don't think the sort order is going to help me select
records
to
delete automatically. (I could do it manually no matter what order
it
was
sorted!)


:

Reverse the sort order.

--
Danny J Lesandrini
(e-mail address removed)
www.amazecreations.com



I read several posts relative to returning top N values. Perhaps
I
am
stupid, but I cannot make the transition to apply similar logic
to
return
bottom values.

I have a table with a primary key called [Key]. It contains
unique
values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234,
10-10C-1234
etc. The letter in each string represents a Rev (Revision)
level.
There may
be many revs. I want to write a query that deletes all but the
latest rev.
I know how to return the MAX value which returns the values
01-02F-9432 and
10-10C-1234 from my values above. These are the records I want
to
keep, the
rest need to be deleted. I cannot think of a good way to delete
all
but the
max value. Any ideas?

 

[tkosel]

I probably did not state my objectives/task very well in my original post.
After further thought and reading this entire dialog I decided that a Query
is not what I needed to accomplish this task. This data is in a table used
to send e-mails to employees notifying them that they need to review a
document. It does this everyday until they complete the document. (I call
this table the "Queue".) Each document is designated with a number and a rev.
Employees are identified with a clock number. Each employee will have
different documents that s/he is required to complete. Once they complete a
document, it is removed from the queue. I have routines to do all this no
problem. The table will only have about 400 records in it at any given time
after the solution is implemented.

The wrinkle comes in that some employees don't complete the requirement
before a new revision is assigned. When this happens, they are sent messages
requiring them to complete two (or more) revisions of the same document,
which has been deemed as unnecessary. Some employees don't have e-mail
addresses, so the messages are sent to their supervisor. Supervisors in some
cases are being overwhelmed by uneeded messages relative to old revisions for
their employees. I don't want to keep placing records into a table without
ever cleaning out the ones no longer needed.

The table contains fields for the [Key], [DocumentNumber], [DocumentRev],
[ClockNumber] and [DeleteFlag] among others. The [Key] is the Primary Key, a
unique identifier consisting of the [DocumentNumber] & [Rev] &
[ClockNumber].

I created a form that opens in "Hidden" mode. The Record Source for the
form is:
SELECT ClockNumber, Key, DocumentNumber, DocumentRev, DeleteFlag FROM
TrainingDocsQueueTable ORDER BY ClockNumber, Key DESC;

This presents the records so that each users records is grouped together
(ClockNumber) with the latest rev for similar documents at the top of each
section for similar documents. (Key). The data looks sort of like this.

1111 10-01B1111 10-01 B 0
1111 10-01A1111 10-01 A 0
2345 20-10D2345 20-10 D 0
2345 20-10C2345 20-10 C 0
ETC.

I then wrote a series of three nested do loops to start at the top record
for a specific clock number. Mark the delete flag for that record as false.
Go to the next record, if the clock number is the same as the previous record
and the document number is the same, mark the delete flag as true. Continue
on through all the records , close the form and run a delete query to delete
all records where the delete flag is True.

This may not be the best way to do it, but I think it works pretty good.
Can anyone see anything wrong with it?

Ken, I am going to fool around with your latest suggesion when I have time,
it looks as if there may be something there that I could have also used.
Thanks to all for your ideas and assistance along the way.

Try this:

DELETE *
FROM YourTable AS T1
WHERE MID(Key,6,1) <>
(SELECT MAX(MID(Key,6,1))
FROM YourTable AS T2
WHERE LEFT(T2.Key,5) = LEFT(T1.Key,5)
AND RIGHT(T2.Key,5) = RIGHT(T1.Key,5));

The way it works is that the subquery is correlated with the outer query on
the five left and right characters of the key, so the subquery returns the
row with the maximum value at the 6th position in the string for those rows.
The outer query is restricted to those rows which don't match this value, so
all but the rows which do are deleted.

Ken Sheridan
Stafford, England

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?

 

[Ken Sheridan]

A 'delete' query along the lines I suggested should work, but you'd need to
correlate the subquery with the outer query on the ClockNumber column as well
as the key's component parts. You'd then be able to dispense with
DocumentNumber, DocumentRev and DeleteFlag columns from the table. As the
first two are derived from the key its not a good idea in principle to have
them as it does leave the door open to inconsistent data. Values derived
from other values should only be stored where one can be legitimately changed
independently of the other, e.g. a UnitPrice value in an Invoices or Orders
record can be derived from a UnitPrice value in a Products record, but if
the latter is subsequently changed the former is not, so its right to store
both, as indeed is done in the sample Northwind database which comes with
Access.

Ken Sheridan
Stafford, England

I probably did not state my objectives/task very well in my original post.
After further thought and reading this entire dialog I decided that a Query
is not what I needed to accomplish this task. This data is in a table used
to send e-mails to employees notifying them that they need to review a
document. It does this everyday until they complete the document. (I call
this table the "Queue".) Each document is designated with a number and a rev.
Employees are identified with a clock number. Each employee will have
different documents that s/he is required to complete. Once they complete a
document, it is removed from the queue. I have routines to do all this no
problem. The table will only have about 400 records in it at any given time
after the solution is implemented.

The wrinkle comes in that some employees don't complete the requirement
before a new revision is assigned. When this happens, they are sent messages
requiring them to complete two (or more) revisions of the same document,
which has been deemed as unnecessary. Some employees don't have e-mail
addresses, so the messages are sent to their supervisor. Supervisors in some
cases are being overwhelmed by uneeded messages relative to old revisions for
their employees. I don't want to keep placing records into a table without
ever cleaning out the ones no longer needed.

The table contains fields for the [Key], [DocumentNumber], [DocumentRev],
[ClockNumber] and [DeleteFlag] among others. The [Key] is the Primary Key, a
unique identifier consisting of the [DocumentNumber] & [Rev] &
[ClockNumber].

I created a form that opens in "Hidden" mode. The Record Source for the
form is:
SELECT ClockNumber, Key, DocumentNumber, DocumentRev, DeleteFlag FROM
TrainingDocsQueueTable ORDER BY ClockNumber, Key DESC;

This presents the records so that each users records is grouped together
(ClockNumber) with the latest rev for similar documents at the top of each
section for similar documents. (Key). The data looks sort of like this.

1111 10-01B1111 10-01 B 0
1111 10-01A1111 10-01 A 0
2345 20-10D2345 20-10 D 0
2345 20-10C2345 20-10 C 0
ETC.

I then wrote a series of three nested do loops to start at the top record
for a specific clock number. Mark the delete flag for that record as false.
Go to the next record, if the clock number is the same as the previous record
and the document number is the same, mark the delete flag as true. Continue
on through all the records , close the form and run a delete query to delete
all records where the delete flag is True.

This may not be the best way to do it, but I think it works pretty good.
Can anyone see anything wrong with it?

Ken, I am going to fool around with your latest suggesion when I have time,
it looks as if there may be something there that I could have also used.
Thanks to all for your ideas and assistance along the way.

Try this:

DELETE *
FROM YourTable AS T1
WHERE MID(Key,6,1) <>
(SELECT MAX(MID(Key,6,1))
FROM YourTable AS T2
WHERE LEFT(T2.Key,5) = LEFT(T1.Key,5)
AND RIGHT(T2.Key,5) = RIGHT(T1.Key,5));

The way it works is that the subquery is correlated with the outer query on
the five left and right characters of the key, so the subquery returns the
row with the maximum value at the 6th position in the string for those rows.
The outer query is restricted to those rows which don't match this value, so
all but the rows which do are deleted.

Ken Sheridan
Stafford, England

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?

 

[tkosel]

I understand your statement about inconsistent data. Actually none of the
data in this table is editable during the process. The DocumentNumber,
DocumentRev and Clock number are NOT derived from the Key, the Key is derived
from the DocumentNumber, DocumentRev and ClockNumber. The employee
(ClockNumber) is assigned a specific DocumentNumber, DocumentRev combination.
This combination of DocumentNumber, DocumentRev is a unique identifier in
the Document Table. Upon assignment, the record is created, the Key built
and that is that. (There are other fields that I am not mentioning such as
e-mail address, etc.) This table is used by Total Access E-Mailer to send
e-mail messages. The items in this table never get changed. The table only
gets read, added to or deleted from. Therefore, while your concern is valid
and noted, I don't have to worry about it. Again, thanks for your input.

A 'delete' query along the lines I suggested should work, but you'd need to
correlate the subquery with the outer query on the ClockNumber column as well
as the key's component parts. You'd then be able to dispense with
DocumentNumber, DocumentRev and DeleteFlag columns from the table. As the
first two are derived from the key its not a good idea in principle to have
them as it does leave the door open to inconsistent data. Values derived
from other values should only be stored where one can be legitimately changed
independently of the other, e.g. a UnitPrice value in an Invoices or Orders
record can be derived from a UnitPrice value in a Products record, but if
the latter is subsequently changed the former is not, so its right to store
both, as indeed is done in the sample Northwind database which comes with
Access.

Ken Sheridan
Stafford, England

I probably did not state my objectives/task very well in my original post.
After further thought and reading this entire dialog I decided that a Query
is not what I needed to accomplish this task. This data is in a table used
to send e-mails to employees notifying them that they need to review a
document. It does this everyday until they complete the document. (I call
this table the "Queue".) Each document is designated with a number and a rev.
Employees are identified with a clock number. Each employee will have
different documents that s/he is required to complete. Once they complete a
document, it is removed from the queue. I have routines to do all this no
problem. The table will only have about 400 records in it at any given time
after the solution is implemented.

The wrinkle comes in that some employees don't complete the requirement
before a new revision is assigned. When this happens, they are sent messages
requiring them to complete two (or more) revisions of the same document,
which has been deemed as unnecessary. Some employees don't have e-mail
addresses, so the messages are sent to their supervisor. Supervisors in some
cases are being overwhelmed by uneeded messages relative to old revisions for
their employees. I don't want to keep placing records into a table without
ever cleaning out the ones no longer needed.

The table contains fields for the [Key], [DocumentNumber], [DocumentRev],
[ClockNumber] and [DeleteFlag] among others. The [Key] is the Primary Key, a
unique identifier consisting of the [DocumentNumber] & [Rev] &
[ClockNumber].

I created a form that opens in "Hidden" mode. The Record Source for the
form is:
SELECT ClockNumber, Key, DocumentNumber, DocumentRev, DeleteFlag FROM
TrainingDocsQueueTable ORDER BY ClockNumber, Key DESC;

This presents the records so that each users records is grouped together
(ClockNumber) with the latest rev for similar documents at the top of each
section for similar documents. (Key). The data looks sort of like this.

1111 10-01B1111 10-01 B 0
1111 10-01A1111 10-01 A 0
2345 20-10D2345 20-10 D 0
2345 20-10C2345 20-10 C 0
ETC.

I then wrote a series of three nested do loops to start at the top record
for a specific clock number. Mark the delete flag for that record as false.
Go to the next record, if the clock number is the same as the previous record
and the document number is the same, mark the delete flag as true. Continue
on through all the records , close the form and run a delete query to delete
all records where the delete flag is True.

This may not be the best way to do it, but I think it works pretty good.
Can anyone see anything wrong with it?

Ken, I am going to fool around with your latest suggesion when I have time,
it looks as if there may be something there that I could have also used.
Thanks to all for your ideas and assistance along the way.

Try this:

DELETE *
FROM YourTable AS T1
WHERE MID(Key,6,1) <>
(SELECT MAX(MID(Key,6,1))
FROM YourTable AS T2
WHERE LEFT(T2.Key,5) = LEFT(T1.Key,5)
AND RIGHT(T2.Key,5) = RIGHT(T1.Key,5));

The way it works is that the subquery is correlated with the outer query on
the five left and right characters of the key, so the subquery returns the
row with the maximum value at the 6th position in the string for those rows.
The outer query is restricted to those rows which don't match this value, so
all but the rows which do are deleted.

Ken Sheridan
Stafford, England

:

I read several posts relative to returning top N values. Perhaps I am
stupid, but I cannot make the transition to apply similar logic to return
bottom values.

I have a table with a primary key called [Key]. It contains unique values
such as: 01-02E-9432, 01-02F-9432, 10-10A-1234, 10-10B-1234, 10-10C-1234
etc. The letter in each string represents a Rev (Revision) level. There may
be many revs. I want to write a query that deletes all but the latest rev.
I know how to return the MAX value which returns the values 01-02F-9432 and
10-10C-1234 from my values above. These are the records I want to keep, the
rest need to be deleted. I cannot think of a good way to delete all but the
max value. Any ideas?