Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2016
    Posts
    1

    Unanswered: Access Query Creating Duplicates

    I made a Query that was working fine for the previous 2 weeks but has broken.
    Its a query between two tables. There is one relationship between the two table being a "Time" column in both tables.
    When it runs, the query runs it creates 196 duplicates of the same information.

    I have attempted to delete the relationship, and nothing changes is the query.

    So as of now the relationship is back in place relating the times from the two tables

    the select statement is- SELECT DISTINCTROW PpmData.RecordTime, PpmData.C2H2, ExternalSensorData.Aux3
    FROM PpmData, ExternalSensorData;

    There are 183 rows in the First table and 193 rows in the Second table but the query is pulling up over 35,000 "results". Being the data that correlates to the "time" in the first table with 196 duplicates.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    I don't see a join in the query. This will make duplicates.

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    This is a Cartesian JOIN ie. it produces every combination of the records from both tables. So the number of records will be 183x193=35,319. This will only create duplicated records if there are duplicate records in at least one of the tables or you only select fields with duplicated data.

    You need to create an INNER JOIN using the related fields (assuming there is one!).

    HTH


    MTB

Tags for this Thread

Posting Permissions

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