Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Deduplicating a table (Access 2000)

    I'm trying to get a table or query containing only ONE example of a particular value, but finding it very tricky!
    I have a table of names, each name is unique. This table is linked to another table where there are more than one record for each name, but not all names from the first table are used (this is important later on).
    I can construct a query to show the names once only (ie the first table), but this shows names not referenced in the second table - this is not wanted!
    If I create a query with the first table name linked to the second table name, then I get only the names referenced in the second table, but several times over, and I only want to see them once!
    The bottom line is that I want to put the second table (or a query using it) on a DAP, using a drop down list in a group section (header section) to select a name's record(s) to display, but if I use the first table for name values, I include those names that do not have equivalents in the second table and the DAP 'freezes' by not displaying anything if one of those is selected! Otherwise I just get multiple instances of a name in the list and that's SO unprofessional!
    So the question is - how do I show names from the second table only once instead of the multiple instances of them in the table?

    Thanks in advance for any ideas or suggestions or help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Deduplicating a table (Access 2000)

    You could create a query based on the second table, add the name field to the query design grid, and set the Unique Values property of the query as a whole to Yes. This is equivalent to a SQL expression starting with SELECT DISTINCT instead of just SELECT.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Nottingham, UK, Nottinghamshire, England
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Deduplicating a table (Access 2000)

    Thanks Hans - that worked a treat, or at least it did once I sorted out a moan from Access about the Filter Control on the Sorting/Grouping for the DAP. That turned out to be because of the Default Sort! The ways of Access are many and various......... But at least my DAP works, thanks to your help, and I've learned something new. 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
  •