delete duplicates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a table Ra that shows like that:
CSPC Pieces

B1574 15,888
B1575 15,888
B3144 10,200
B3145 10,200
B3024 6,500
B3025 5,555
I need to get another table to show like that:

B1574 0
B1575 15,888
B3144 0
B3145 10,200
B3024 6,500
B3025 5,555
Could you please help?
Thank you,

Ra
 
This appears to work. Use your table name instead of Ra_x.

SELECT Ra_x.CSPC, Min(IIf(Left([Ra_x].[CSPC],1) &
(Val(Right([Ra_x].[CSPC],4))+1)=[Ra_x_1].[CSPC] And
[Ra_x].[Pieces]=[Ra_x_1].[Pieces],0,[Ra_x].[Pieces])) AS [Number of Pieces]
FROM Ra_x, Ra_x AS Ra_x_1
GROUP BY Ra_x.CSPC;
 
Thank you so much...it is working !!!

KARL DEWEY said:
This appears to work. Use your table name instead of Ra_x.

SELECT Ra_x.CSPC, Min(IIf(Left([Ra_x].[CSPC],1) &
(Val(Right([Ra_x].[CSPC],4))+1)=[Ra_x_1].[CSPC] And
[Ra_x].[Pieces]=[Ra_x_1].[Pieces],0,[Ra_x].[Pieces])) AS [Number of Pieces]
FROM Ra_x, Ra_x AS Ra_x_1
GROUP BY Ra_x.CSPC;

--
KARL DEWEY
Build a little - Test a little


Ra said:
Hi,

I have a table Ra that shows like that:
CSPC Pieces

B1574 15,888
B1575 15,888
B3144 10,200
B3145 10,200
B3024 6,500
B3025 5,555
I need to get another table to show like that:

B1574 0
B1575 15,888
B3144 0
B3145 10,200
B3024 6,500
B3025 5,555
Could you please help?
Thank you,

Ra
Click to expand...
Click to expand...
 
Back
Top