Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: How to remove DUPLICATES

    I would like to remove duplicates from the following query. I would like it to display only one record depending on the InvoiceID. So only show one unique record based on Invoices.InvoiceID. Thanks!

    SELECT DISTINCT Invoices.InvoiceID, Invoices.CustomerID, InvoiceDetails.InvoiceDetailID, Invoices.InvoiceDate
    FROM InvoiceDetails INNER JOIN Invoices ON InvoiceDetails.InvoiceID = Invoices.InvoiceID
    WHERE (((Invoices.InvoiceDate) Between #8/1/2004# And #8/31/2004#) AND ((InvoiceDetails.DeliverBy)=0))
    ORDER BY Invoices.InvoiceID;

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Well...you might try this....

    (Assuming that you are pulling the InvoiceID required from a field on Form)

    Code:
    SELECT DISTINCT Invoices.InvoiceID, Invoices.CustomerID, InvoiceDetails.InvoiceDetailID, Invoices.InvoiceDate
    FROM InvoiceDetails INNER JOIN Invoices ON InvoiceDetails.InvoiceID = Invoices.InvoiceID
    WHERE (((Invoices.InvoiceDate) Between #8/1/2004# And #8/31/2004#) AND ((InvoiceDetails.DeliverBy)=0) _
    AND ((Invoices.InvoiceID) = '" & Me.myFormsInvoiceIDFieldControlName & "'"))
    ORDER BY Invoices.InvoiceID;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    CyberLynx, that will cut down the number of returned invoices to 1, but it will do nothing about the one-to-many relationship between an invoice and its invoice details

    jessn, if you want to display only one row per invoice, which of the multiple invoice details do you want? the first? the last? the one with the largest price?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2004
    Posts
    16
    Hi,

    I've a similar problem... I have a duplicates query, and I want to see only unique records with their most recent date.

    for example:

    LAW Name1 2/3/2004
    HR Name1 8/6/2004
    TIS Name1 7/31/2004
    CPM Name2 3/8/2003
    TIS Name2 12/30/2003

    In the above examples, for Name1 I want to retain the HR 8/6 record, and for Name2 I want to retain the TIS 12/30 record.

    Help!

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    lastknightess, your question is really different from the other question in this thread, it should perhaps have been its own thread

    but here you go anyway:
    Code:
    select dept
         , name
         , foodate
      from yourtable as x
     where foodate
         = (select max(foodate)
              from yourtable
             where name = x.name )
    this query uses a correlated subquery to perform the necessary grouping
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2004
    Posts
    16
    ^ i didn't realise it was different.. jessn, my apologies for hijacking your thread.

    rudy, i opened up a separate thread for answers to my query, here is the link
    http://www.dbforums.com/t1021825.html

    i need some more help... i posted what my query looks like right now, can u plz help me modify it? plz... i hope to see u there. thanks.

    LK

Posting Permissions

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