Results 1 to 10 of 10
  1. #1
    BannedOak is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3

    Filtering

    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

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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.

  3. #3
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Please don't repeat same thread there is an answer for this post.

  4. #4
    BannedOak is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by Bob Fitz View Post
    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?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    BannedOak is offline Novice
    Windows XP Access 2003
    Join Date
    May 2011
    Posts
    3
    Quote Originally Posted by June7 View Post
    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?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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 [].
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    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.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2007
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Hi June7

    I agree about the matching records. If the OP requests help with that I'll be happy to take a look.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering a report
    By tarhim47 in forum Reports
    Replies: 2
    Last Post: 03-30-2011, 11:49 AM
  2. Report Filtering..
    By banjo1t in forum Reports
    Replies: 1
    Last Post: 01-24-2011, 06:56 AM
  3. filtering
    By nashr1928 in forum Forms
    Replies: 12
    Last Post: 07-01-2010, 06:30 AM
  4. Filtering a Report
    By musicalogist in forum Reports
    Replies: 12
    Last Post: 04-29-2010, 08:36 PM
  5. Filtering a Query
    By jbarrum in forum Access
    Replies: 1
    Last Post: 11-20-2009, 03:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums