access数据库剔除重复项_在Access中删除SQL中的重复项

I have a table that consists of the columns id(key), symbol, direction, range, price, percent. I would like to delete the data where the symbol, direction, price, and percent are the same that has the lowest range. How would I accomplish this?

I have been trying to alter this statement: DELETE

FROM aw

WHERE id not in (select min(id) from aw group by symbol, direction, price, percent); to get something to work.

解决方案

Here is sample data for the aw table. Based on your description, I think you want to discard the rows with ID values of 2, 3, and 5.

ID symbol direction range price percent

1 a x 15 10 5

2 a x 20 10 5

3 b y 40 50 5

4 b y 10 50 5

5 a x 15 10 5

Determine the minimum range value for each combination of symbol, direction, price, and percent.

qryMinRanges:

SELECT

aw.symbol,

aw.direction,

aw.price,

aw.[percent],

Min(aw.range) AS MinOfrange

FROM aw

GROUP BY

aw.symbol,

aw.direction,

aw.price,

aw.[percent];

... which gives this result set:

symbol direction price percent MinOfrange

a x 10 5 15

b y 50 5 10

Determine the minimum ID for each of those minimum ranges.

qryMinID_forMinRanges:

SELECT

q.symbol,

q.direction,

q.price,

q.[percent],

q.MinOfrange,

Min(aw.ID) AS MinOfID

FROM

qryMinRanges AS q

INNER JOIN aw

ON

(q.MinOfrange = aw.range)

AND (q.[percent] = aw.[percent])

AND (q.price = aw.price)

AND (q.direction = aw.direction)

AND (q.symbol = aw.symbol)

GROUP BY

q.symbol,

q.direction,

q.price,

q.[percent],

q.MinOfrange;

... which gives this result set:

symbol direction price percent MinOfrange MinOfID

a x 10 5 15 1

b y 50 5 10 4

So qryMinID_forMinRanges should represent the rows you want to keep. Ultimately you will delete the rows from aw whose ID values are not included in qryMinID_forMinRanges. But first try this SELECT query to confirm you're targeting the correct records for deletion.

SELECT

aw.ID,

aw.symbol,

aw.direction,

aw.range,

aw.price,

aw.[percent]

FROM aw

WHERE aw.ID Not In

(SELECT MinOfID FROM qryMinID_forMinRanges);

... which gives me this result set:

ID symbol direction range price percent

2 a x 20 10 5

3 b y 40 50 5

5 a x 15 10 5

So, if that looks correct, change it to a DELETE query.

DELETE *

FROM aw

WHERE

(((aw.ID) Not In

(SELECT MinOfID FROM qryMinID_forMinRanges)));

The db engine doesn't require the * after DELETE. But if you want to use the query designer's preview, you need to give it a field spec to display.

Please take precautions to safeguard your data in case I screwed up any details here. Backups are good. :-)