Filtering

[BannedOak]

Hello,



I'm completely new in using the Access and I cannot manage how to clear my large database. My problem is as follows, I have a database like this (ID|Name|Number):

1|BannedOak|2.46
2|BannedOak|3.85
3|BannedOak|8.5
4|BannedOak|12
5|BannedOak|25
6|BannedOak|1.02
7|Someone|3.14
8|Someone|6.8
9|Someone|11

What I need is to find out the line with the unique Name and maximum value of Number, and minimum value of Number. So, after this the database would consist:
5|BannedOak|25
6|BannedOak|1.02
7|Someone|3.14
9|Someone|11


Can someone give me a hint or a solution how to do that?

Thanks in advance,
Dean

[Bob Fitz]

Hi
Do you really want to delete the data between the min and max values?
If you just want to View/work with/base a reoprt/etc with the min and max records for each Name this could easily be done with a query.

[khalid]

Please don't repeat same thread there is an answer for this post.

[BannedOak]

Hi
Do you really want to delete the data between the min and max values?
If you just want to View/work with/base a reoprt/etc with the min and max records for each Name this could easily be done with a query.

yes, I want to delete the data between min and max values. how to do that automatically?

[June7]

Delete data, sacrilege! Are you sure you won't regret this? The purpose of queries is to manipulate raw data to produce desired output without actually eliminating anything. That is the solution Khalid is suggesting. It returns the Min and Max values for each Name. This will not tell which records provide the Min and Max but is that important?

However, if you must delete, first do a query that retrieves the IDs that have the Min and Max values. This won't be easy.

Then build DELETE query based on the first:
DELETE FROM tablename WHERE NOT ID IN firstqueryname;

Our point is that if the first query can be accomplished, why bother with the DELETE?

[BannedOak]

Delete data, sacrilege! Are you sure you won't regret this? The purpose of queries is to manipulate raw data to produce desired output without actually eliminating anything. That is the solution Khalid is suggesting. It returns the Min and Max values for each Name. This will not tell which records provide the Min and Max but is that important?

However, if you must delete, first do a query that retrieves the IDs that have the Min and Max values. This won't be easy.

Then build DELETE query based on the first:
DELETE FROM tablename WHERE NOT ID IN firstqueryname;

Our point is that if the first query can be accomplished, why bother with the DELETE?

Why delete? Simply, my database consists of over 4 million records during one month (with all records, not deleted) and I have to do one database for last 8 years. So my complete database would cosist of approx. 400 million recods. That's why I need to delete other than min/max records and shrink it to 1/20 th of the complete database. Good reason?

[June7]

Good reason maybe to graduate to some SQL app. But that is a LOT of records, don't know what kind of limit SQL has. So basically you need to reduce raw data to two relevant records, the Max and Min. Is 1/20th even small enough for Access? It's not really the number of records but file size (2 gigs) that Access limits. And not just tables use up that capacity - queries, forms, reports, code also count. So make sure tables are as normalized and compact as possible and run Compact & Repair periodically, split the database.

Okay, SELECT query could be something like (easier than I expected):
SELECT ID, [Name], [Number]
FROM tablename
WHERE ((([Number])=DMax("[Number]","tablename","[Name]='" & [Name] & "'"))) OR ((([Number])=DMin("[Number]","tablename","[Name]='" & [Name] & "'")));

Warning, aggregate functions can be very slow with a LOT of records. Maybe SELECT subqueries could be used instead of the DMax/DMin. Is this a split database? Are you doing this over a network?

However, if more than one record can meet the Max and Min criteria they will all be retrieved and not be deleted. Will then have to do find and remove duplicates (retain records with Max ID or date).

BTW, might want to avoid using reserved words as fields name, if do must enclose in [].

[Bob Fitz]

Hi
If you are still sure that you want to delete these records, then the following delete query could be modified to do this.

MAKE A BACKUP COPY BEFORE YOU START.

DELETE TableName.[NumberField]
FROM TableName
WHERE (((TableName.[NumberField])<>DMax("[NumberField]","tablename","[FieldName]='" & [FieldName] & "'") And (TableName.[NumberField])<>DMin("[NumberField]","tablename","[FieldName]='" & [FieldName] & "'")));

You will need to change TableName to the name of your table.
You will need to change NumberField to the name of the field in your table that holds the numbers.
You will need to change FieldName to the name of the field in your table that holds the Name Data.

[June7]

Thanks Bob, I felt that this should be doable in one query but just didn't quite get there. However, the possibility of multiple records meeting the Max or Min values still holds.

[Bob Fitz]

Hi June7

I agree about the matching records. If the OP requests help with that I'll be happy to take a look.