1

I have the following table in Access

enter image description here

I have a number of lines where there are duplicates when you filter on the first and second column. I need to remove these duplicates. The best way I found is to use row_number.

I want to emulate the equivalent of this Postgres query in Microsoft Access:

SELECT 
    "Supplier_code", 
    "Internal_reference", 
    "Supplier_reference", 
    "Family_code", 
    "Purchasing_unit", 
    "Purchasing_price",
    "Purchasing_currency",
    "Consigned",
    "Eco_order_qty",
    "Pack_order_qty",
    "Min_order_qty",
    "Min_order_value",
    "Product_grossweight",
    "Product_grosscube",
    "Leadtime_days",
    "Supplier_active",
ROW_NUMBER() OVER (PARTITION BY "Supplier_code", "Internal_reference" 
ORDER BY "Internal_reference") AS "cnt"
FROM dw.T13_RefSupplier_Mexico_ToXL

I had a look on the internet and I found this on Stack Overflow but I'm not sure where to start:

This table is a buffer table and has no PK. All duplicate data must be purged from this table. Once done, it will go to the main table where Supplier_code & Internal_referenceare part of the PK.

I built this query but it does not achieve what I want:

 SELECT 
    t1.Supplier_code,
    t1.Internal_reference,
    t1.Supplier_reference,
    t1.Family_code,
    t1.Purchasing_unit,
    t1.Purchasing_price,
    t1.Purchasing_currency,
    t1.Consigned,
    t1.Eco_order_qty,
    t1.Pack_order_qty,
    t1.Min_order_qty,
    t1.Min_order_value,
    t1.Product_grossweight,
    t1.Product_grosscube,
    t1.Leadtime_days,
    t1.Supplier_active,
    COUNT(*) AS [Ino]
FROM 
    T13_RefSupplier_Mexico_ToXL AS t1
    INNER JOIN
    T13_RefSupplier_Mexico_ToXL AS t2
        ON t2.Supplier_code = t1.Supplier_code
            AND t2.Purchasing_price <= t1.Purchasing_price
GROUP BY
    t1.Supplier_code,
    t1.Internal_reference,
    t1.Supplier_reference,
    t1.Family_code,
    t1.Purchasing_unit,
    t1.Purchasing_price,
    t1.Purchasing_currency,
    t1.Consigned,
    t1.Eco_order_qty,
    t1.Pack_order_qty,
    t1.Min_order_qty,
    t1.Min_order_value,
    t1.Product_grossweight,
    t1.Product_grosscube,
    t1.Leadtime_days,
    t1.Supplier_active
ORDER BY 1, 2

Please find an extract of my data below

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;
T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;
T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;
T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;
T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;
T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;
T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;
T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;
T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;
T080;16-0067;;402;EA;0.47;USD;0;0;;;;;;0;1;
T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;
T900;53-2008;;391;EA;0.033;USD;0;0;;;;;;63;1;

The desired result can either be the solution, where the duplicates are removed

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;
    T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;
    T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;
    T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;
    T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;
    T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;
    T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;
    T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;
    T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;
    T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;

Or the solution where the duplicates are flagged

Supplier_code;Internal_reference;Supplier_reference;Family_code;Purchasing_unit;Purchasing_price;Purchasing_currency;Consigned;Eco_order_qty;Pack_order_qty;Min_order_qty;Min_order_value;Product_grossweight;Product_grosscube;Leadtime_days;Supplier_active;Duplicate
T040;16-0022;;401;EA;0.072;USD;0;0;;;;;;42;1;1
T040;50-0595A;;401;EA;0.163;USD;0;0;;;;;;42;1;1
T070;50-0672;;131;EA;0.0693;USD;0;0;;;;;;63;1;1
T070;50-0673;;131;EA;0.0755;USD;0;0;;;;;;63;1;1
T070;50-0687;;131;EA;0.1097;USD;0;0;;;;;;63;1;1
T070;50-0688;;131;EA;0.0899;USD;0;0;;;;;;63;1;1
T070;50-0738;;131;EA;0.124;USD;0;0;;;;;;0;1;1
T080;16-0067;;402;EA;0.47;EUR;0;0;;;;;;0;1;1
T080;16-0067;;402;EA;0.47;USD;0;0;;;;;;0;1;2
T900;53-2008;;391;EA;0.039;USD;0;0;;;;;;63;1;1
T900;53-2008;;391;EA;0.033;USD;0;0;;;;;;63;1;2

I cannot create an autoincrement in my query as it is not a real table, more the results of a query. See the picture in Access:

enter image description here

2

A "triangular" self-join and group by or - the equivalent - inline self-join and count should work (but I'm not really sure if it fails due to some Access restrictions):

SELECT 
    t.*,
    ( SELECT COUNT(*) 
      FROM dw.T13_RefSupplier_Mexico_ToXL AS c
      WHERE       -- PARTITION BY these 2 columns 
            c."Supplier_code" = t."Supplier_code" 
        AND c."Internal_reference" = t."Internal_reference" 
                  -- ORDER BY these 2
        AND ( c."Purchasing_currency" < t."Purchasing_currency" 
           OR (  c."Purchasing_currency" = t."Purchasing_currency" 
             AND c."Purchasing_price" <= t."Purchasing_price"
              )
            )
    ) AS cnt
FROM 
    dw.T13_RefSupplier_Mexico_ToXL AS t ;
2

IMHO you don't need a simulated ROW_NUMBER function just to avoid duplicates, you can easily get it by adding a unique field, in this case an AUTO-INCREMENT field. If you cannot made it PRIMARY KEY, at least set an index on it without duplicates.

Let's name this field ID.

Then you can use a view or a subquery that returns MIN(ID) grouped by Supplier_code; Internal_reference

SELECT   Min(ID) as ID, Supplier_code, Internal_reference
FROM     T13_RefSupplier_Mexico_ToXL
GROUP BY Supplier_code, Internal_reference

Last step, JOIN this view with your current query:

SELECT     ...
FROM       T13_RefSupplier_Mexico_ToXL t1
INNER JOIN TheNewView nv
ON         t1.ID = nv.ID
AND        t1.Supplier_code = nv.Supplier_code
AND        t1.Internal_reference = nv.Internal_reference

I've set up a small MS-Access project with the provided data, and it works fine.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.