delete duplicates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ra - 14 Nov 2007 23:42 GMT

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

Reply to this Message

KARL DEWEY - 15 Nov 2007 00:36 GMT

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;

Signature

KARL DEWEY
Build a little - Test a little

> Hi,
>
[quoted text clipped - 19 lines]
>
> Ra

Reply to this Message

Ra - 15 Nov 2007 12:25 GMT

Thank you so much...it is working !!!

> This appears to work. Use your table name instead of Ra_x.
>
[quoted text clipped - 27 lines]
> >
> > Ra

Reply to this Message