Access question. Can I delete a row with the minimum value or maxium value?

Question

Maybe you can help. I'm trying to make a database of average cost per item. I want to toss out one (and only one) maximum item and one and only one minimum item. Then I want to find what average is left. (i.e. a certain $1,000,000 item is skewing the table). Can this be done easily in access or do I need to do some VBA coding? I tried using a "tickler file" and do a join, but it deletes every single minimum (or maximum) item.

I am allowed to create as many tables as I need to do this, so space or authority is not a problem.

Thank you.

Answer 1

Create these queries:

This selects one record. Substitute min or max as needed.

SELECT Min(Table1.Field1) AS MinOfField1
FROM Table1;

This deletes one record

DELETE Table1.Field1
FROM Table1

WHERE (((Table1.Field1)=
DLookUp("[MinOfField1]",
"Q_Select")));

Make sure you join everything between WHERE and the ending ; on one line. For some reason it won't all display here on one line.

Experiment.

Only one record gets deleted, not the entire table.

Answer 2

I'd have to see it to be sure, but one easy 'hard-code' fix may be

Build a query pulling from the table and use the Criteria to explicitly remove the data that's skewing your results
( Not 1000000 AND Not 10 )

Then use the data from that query to pull your averages.

Unless that excludes some data from your other categories, that may be the simplest.