Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    361

    Unanswered: Duplicates in make table query

    I have a make table query that gets data from 5 or so other tables. For some reason I get 3 numbers that are duplicates. There always the same ever time I run the query. In the query I have a group by in the total row. I guess this is supposed to cure the duplicate problem. Are there anyother steps I can follow to stop this problem? Thanks a bunch.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Use DISTINCT or DISTINCTROW ... In the SELECT of course ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Posts
    361
    Here is part of the select code that I have.

    SELECT DISTINCT Leases.LEASE_NO, Leases.o_maty, [tbl Main].[Sale Price], [tbl Main].[Excess Mileage], [tbl Main].[Daily Lease Charge], [tbl Main].EWT, [tbl Main].[Other Funds],

    and it goes on and on and on. Is this the right place to put the work DISCTINCT if I want a distinct LEASE_NO column?

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Code:
    SELECT *
    from
      (select DISTINCT l.LEASE_NO, l.o_maty, 
      from lease l) V1
    INNER JOIN
      (select m.[Sale Price], m.[Excess Mileage], m.[Daily Lease Charge], 
      m.EWT, m.[Other Funds], tb.column1, tb.column2
      from [tbl main] m
      INNER JOIN
      tableB tb ON
      m.id = tb.id) V2 ON
    V1.id = V2.id
    Last edited by r123456; 06-25-04 at 22:18.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Mar 2004
    Posts
    361
    I tried to add that code to my select statement but I keep getting errors. I tried this and still creates duplicates.

    SELECT DISTINCTROW Leases.LEASE_NO, Leases.o_maty, [tbl Main].[Sale Price], [tbl Main].[Excess Mileage], [tbl Main].[Daily Lease Charge], [tbl Main].EWT, [tbl Main].[Other Funds], [tbl Main].[Transport Fee], [tbl Main].[Auction Sale Fee]

    What is odd is that none of the 3 tables have these three duplicated values in them, but when i run the build table query it creates them. Is there any other way to avoid this?

Posting Permissions

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