Delete the correct fields

[Guest]

This is a new one for me and I am just not getting it.

I am importing data:

[dept], [class], [Revenue] etc

I was not able to filter out a "total" line on the import so I need to build
a delete query to do it.

Pulling the records into a query and specifying "13" for [class] gives me
two records for each [dept]. I need to specify (delete) the record in each
dept where [revenue] is greatest.

Can someone help? Thank you

 

[Guest]

Jeff:

Try something like this:

DELETE *
FROM YourTable AS T1
WHERE Class = "13"
AND Revenue =
(SELECT MAX(Revenue)
FROM YourTable AS T2
WHERE T2.Dept = T1.Dept
AND T2.Class = "13");

Note, however, that if the Revenue value is the same in the two rows, both
would be deleted.

Ken Sheridan
Stafford, England

 

[Guest]

Cool . . Thanks Ken
--
Jeff C
Live Well .. Be Happy In All You Do

Jeff:

Try something like this:

DELETE *
FROM YourTable AS T1
WHERE Class = "13"
AND Revenue =
(SELECT MAX(Revenue)
FROM YourTable AS T2
WHERE T2.Dept = T1.Dept
AND T2.Class = "13");

Note, however, that if the Revenue value is the same in the two rows, both
would be deleted.

Ken Sheridan
Stafford, England

This is a new one for me and I am just not getting it.

I am importing data:

[dept], [class], [Revenue] etc

I was not able to filter out a "total" line on the import so I need to build
a delete query to do it.

Pulling the records into a query and specifying "13" for [class] gives me
two records for each [dept]. I need to specify (delete) the record in each
dept where [revenue] is greatest.

Can someone help? Thank you