Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    remove duplicates from query results (xp)

    Hello

    How do I remove the a record when it appears in a query recordset more than once? For example, I have a training tracking database. When I query for who has attended a given class name, I only want to see each name once - even if the student attended the class multiple times on different dates.

    Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: remove duplicates from query results (xp)

    If you built the query in the query grid, change to SQL view ( using the View Menu) , and add the word DISTINCT immediuately after SELECT.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove duplicates from query results (xp)

    Thanks for the quick response. I'm still having trouble though.

    With no space between SELECT and DISTINCT, I get an error message that says "Invalid SQL statement; expected "DELETE", "INSERT", "PROCEDURE", "SELECT", or "UPDATE". "
    With a space, there is no error but the "duplicates" are still there.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: remove duplicates from query results (xp)

    Are the duplicates completely the same for all fields returned ?

    If your query returns the training date, for instance, then the rows of data are not identical, and so 'duplicates' are not removed.

    Putting in DISTINCT removes duplicates where every field in the returned data matches another one.
    Regards
    John



  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Chicago
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: remove duplicates from query results (xp)

    Sorry for the confusion.

    No - they are not total duplicates, some of the fields are different. Is there a way to omit records based on one field in the recordset being duplicative?

    Thanks

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: remove duplicates from query results (xp)

    If the rows are not total duplicates, then the task you face is not that of removing duplicates.

    Instead what you are trying to do is SELECT just some rows of data to be returned. You need to be clear about just which ones you want and which ones you don't want.

    For example, you might want the most recent training date, or perhaps the first training date.

    Exactly how you do it will depend on how many fields are involved.

    You might be able to do it by changing the query to a grouping query by clicking the Totals buttton on the toolbar. This will add an extra line to the grid, with Group By in each field.

    If you grouped by Person Name, and found the Max Training date it would return the latest training date for each person.

    If things are more complicated, then you will need to do the job in two stages. Build a grouping query , then join the results back to one or more tables to return all the fields you want.
    Regards
    John



Posting Permissions

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