Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Unanswered: Filter using Query

    ---- Anyone would like to help. Pleeeeease -----
    Please see attach database while reading my request.

    1. There are three primary keys (GA_Number, PYE, PlanNum)
    2. I would like to filter out all the records except duplicate primary key (but I want the latest PYE-Plan Year End).
    3. For example,

    GA_Number GA_Name PYE PlanNum
    07538 MSN 12/31/2001 01
    07538 MSN 12/31/2002 01

    4. When I filter I should only see the second record with pye 12/31/02.

    5. There are some record that does not have duplicate record and I would like to keep those.

    6. So, I did a manual filter on tbl_groups_NW_old and my total was 83 records.

    ---------------------------------------------------------------------------------
    Purpose of this: Every now and then we run a query to find out how many records (clients) we are administrating. Every year we create the same record but with different PYE (plan year end) but ga_number and PlanNum stays the same.

    A good example would be GA_Number 44268. We are going to keep ga_record_id 1194 instead of 200 b/c it has the most recent PYE.

    There are some record does not have multiple record. We certainly want to keep those. For example, GA_Number 24002.

    ------------------------------------------------------------------------------
    PROMPT RESPOND WILL BE VERY VERY APPRECIATED.
    Last edited by doran_doran; 09-16-03 at 01:26.

  2. #2
    Join Date
    Apr 2003
    Location
    Homer, Alaska
    Posts
    15
    You can do this by creating 2 queries
    First query:

    SELECT tbl_groups.GA_Number, Max(tbl_groups.PYE) AS MaxOfPYE, tbl_groups.PlanNum
    FROM tbl_groups
    GROUP BY tbl_groups.GA_Number, tbl_groups.PlanNum;


    This will give you a single record for each unique GA_Number & PlanNum
    with the latest PYE

    the second query will give you the entire table's worth of data with the duplicates excluded.
    Second query:

    SELECT tbl_groups.*
    FROM tbl_groups INNER JOIN Query1 ON (tbl_groups.PlanNum = Query1.PlanNum) AND (tbl_groups.PYE = Query1.MaxOfPYE) AND (tbl_groups.GA_Number = Query1.GA_Number);


    Note: Replace "Query1" with whatever name you've given the first query.

  3. #3
    Join Date
    Dec 2002
    Location
    Houston, Texas
    Posts
    74

    Smile THANK YOU !! THANK YOU !! THANK YOU

    tsulczynski,

    Thanks a million for the quick repond. It worked. Really appreciated. Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •