none
If duplicates in a field in a table, keep only the highest number from another field. RRS feed

  • Question

  • Hi everyone,

    I have a table that is an history of a production order (Begins by BP-) everytime a user enter or change information on an order, the program creates a new record, I would like to keep only the last record from the field MAHFAC_NO_LIGNE, that way I would have only the current status for that order. Could be a query or SQL statement

    Here is an image:

    Thank you for your time

    Claude from Quebec


    Claude Larocque

    Friday, February 9, 2018 8:37 AM

Answers

  • Try this delete query:

    DELETE MAHFAC_NO_LIGNE FROM [TableName] WHERE DCount("*", "[TableName]", "MAHFAC_DOCUMENT = '" & [MAHFAC_DOCUMENT] & "'") > 1 AND MAHFAC_NO_LIGNE < DMax("MAHFAC_NO_LIGNE", "[TableName]", "MAHFAC_DOCUMENT = '" & [MAHFAC_DOCUMENT] & "'")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 9, 2018 9:51 AM

All replies

  • Try this delete query:

    DELETE MAHFAC_NO_LIGNE FROM [TableName] WHERE DCount("*", "[TableName]", "MAHFAC_DOCUMENT = '" & [MAHFAC_DOCUMENT] & "'") > 1 AND MAHFAC_NO_LIGNE < DMax("MAHFAC_NO_LIGNE", "[TableName]", "MAHFAC_DOCUMENT = '" & [MAHFAC_DOCUMENT] & "'")


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, February 9, 2018 9:51 AM
  • ciao Claude,

    try this way, customizing table name and fields of your scenario :

    delete * from T_ordine as T

    where exists ( select
                                      ordine
                             from
                                       T_ordine as o
                             where
                                        o.ordine=t.ordine
                              group by
                                          ordine
                               having count(*)>1 and max(o.idordine)<>t.idordine)

    ciao, Sandro.

    Friday, February 9, 2018 2:02 PM
  • I have a table that is an history of a production order (Begins by BP-) everytime a user enter or change information on an order, the program creates a new record, I would like to keep only the last record from the field MAHFAC_NO_LIGNE, that way I would have only the current status for that order. Could be a query or SQL statement

    Hi Claude,

    Instead of a routine that cleans up all obsolete production orders, can't you modify the import function to automatically delete or overwrite the old production order?

    Imb.

    Friday, February 9, 2018 7:37 PM
  • DELETE *
    FROM public_mahat AS PM1
    WHERE mahfac_no_ligne <>
         (SELECT MAX(mahfac_no_ligne)
          FROM public_mahat AS PM2
          WHERE PM2.mahfac_document = PM1.mahfac_document);

    Ken Sheridan, Stafford, England

    Friday, February 9, 2018 11:34 PM
  • Hi Imb,

    Thanks for taking the time to suggest me something, however, Hans answer works at first try, that was exactly what I was looking for, thanks again.

    Claude


    Claude Larocque

    Saturday, February 10, 2018 7:18 AM
  • Thanks Ken,

    Yours work as well, good to have good programmers like you helping others...

    Claude


    Claude Larocque

    Saturday, February 10, 2018 7:19 AM