Results 1 to 4 of 4
  1. #1
    JimmD43 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Posts
    12

    How to not show duplicate records in query?

    I have a query based on two tables, one of which allows multiple values for the primary key. However, I want this query to show only one of each duplicate record. I know I can do this by setting the Totals to first, last, etc. However, if I do this, Access no longer allows me to update the field values in the query. Anyone have any suggestions?

  2. #2
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Hi Jimm
    The resulting query of two or more tables or is not updatable, to just suppress the duplicate, you can use
    SELECT DISTINCT fieldname1, fieldname2, fieldnamen.... FROM table1

    Below are the reasons why recordset is not updatable:

    When Recordsets Are Never Updateable

    A recordset is never updateable when:
    It is based on a Crosstab query.
    It is based on a Union Query.
    It is an Aggregate Query that calculates a sum, average, count or other type of total on the values in a field.
    It is an Update Query that references a field in the Update To row from either a crosstab query, select query, or subquery that contains totals or aggregate functions
    Note: By using a domain aggregate function in the Update To row of an update query, you can reference fields from either a crosstab query, select query, or subquery that contains totals or aggregate functions.
    It is based on a Query that includes a linked ODBC table with no unique index.
    The database was opened as read-only or is located on a read-only drive.
    It is a SQL pass-through query.
    It is a query whose UniqueValues property is set to Yes. (That is, it is a query with a DISTINCT predicate.)
    Cartesian Joins (that is, a query that includes more than one table or query, and the tables or queries aren't joined by a join line in Design view.)
    Query based on three or more tables in which there is a many-to-one-to-many relationship.
    Note: Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).
    Calculated fields. Even if the query itself is updateable, if a column in a query is based on a formula, the field cannot be updated. However, if the other fields in the formula are updated, the calculated field will automatically update.

  3. #3
    JimmD43 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Posts
    12
    Thank you for all the great information. I have, however, tried DISTINCT with no success. The duplicate entries still appear.

  4. #4
    khalid's Avatar
    khalid is offline MS-Access Developer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2010
    Location
    Kuwait
    Posts
    244
    Did you use the Group by aggregate function? but remember if any column value is changed, Group by will also repeat the changed columns values.

    It's difficult to analyse without seeing your queries and tables. you can not update records in group by queries also.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-03-2011, 01:36 PM
  2. Query to show only specific records
    By CEV in forum Queries
    Replies: 5
    Last Post: 03-30-2011, 09:42 AM
  3. Duplicate Records
    By softspoken in forum Queries
    Replies: 3
    Last Post: 06-21-2010, 03:33 PM
  4. Replies: 1
    Last Post: 05-21-2010, 02:22 PM
  5. Delete duplicate records
    By Zukster in forum Queries
    Replies: 1
    Last Post: 08-26-2009, 03:14 AM

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