Keeping, but not counting, duplicate entries

G

GD

I have a small table that has duplicate invoice entries that are supposed to
be there. What I need is to add a field that displays the invoice amount for
the 1st instance (i.e. earliest record) of the invoice # in the table, but
then lists 0.00 for all subsequent records using that invoice #:

Inv# InvAmt NewField
102214 7841.23 7841.23
102513 5619.6 5619.6
102513 5619.6 0.00
102739 8789.61 8789.61
102739 8789.61 0.00
102856 1300.49 1300.49
102856 1300.49 0.00
103027 2637.8 2637.8
103211 4226.75 4226.75
103366 4431.26 4431.26
103479 2139.87 2139.87
103603 3198.97 3198.97
103603 3198.97 0.00

Any query or SQL that might accomplish this?

THANKS!!!
 
G

GD

I forgot to mention that these duplicate records are often NOT consecutive,
as they appear in my example. Several records often separate these
duplications. Figured that might make a difference.
 
K

KARL DEWEY

One way is to add an autonumber field, run a make-table query, and then
update the field based on Rank > 1.
This query will apply rank and make the new table ---
SELECT Q.NewField, Q.InvAmt, Q.[Inv#], Q.Auto, (SELECT COUNT(*) FROM [GD] Q1
WHERE Q1.[InvAmt] = Q.[InvAmt]
AND Q1.[Inv#] = Q.[Inv#]
AND Q1.[Auto] < Q.[Auto])+1 AS Rank INTO GD_Sorted
FROM GD AS Q
ORDER BY Q.InvAmt, Q.Auto;
 
G

GD

Thanks, Karl! I'm having a little trouble following your SQL (I'm pretty new
at this kind of query). Are Q & Q1 the standard designations for a make
table query's original and destination tables in SQL? Or do the actual table
names get used? And are you saying you want the new table to be called GD?

--
GD


KARL DEWEY said:
One way is to add an autonumber field, run a make-table query, and then
update the field based on Rank > 1.
This query will apply rank and make the new table ---
SELECT Q.NewField, Q.InvAmt, Q.[Inv#], Q.Auto, (SELECT COUNT(*) FROM [GD] Q1
WHERE Q1.[InvAmt] = Q.[InvAmt]
AND Q1.[Inv#] = Q.[Inv#]
AND Q1.[Auto] < Q.[Auto])+1 AS Rank INTO GD_Sorted
FROM GD AS Q
ORDER BY Q.InvAmt, Q.Auto;

--
Build a little, test a little.


GD said:
I forgot to mention that these duplicate records are often NOT consecutive,
as they appear in my example. Several records often separate these
duplications. Figured that might make a difference.
Click to expand...
Click to expand...
 
K

KARL DEWEY

The orignal table name is GD. Q and Q1 are aliases of the orignal table and
second instance of the orignal table. GD_Sorted is the new table name as
indicated by 'INTO GD_Sorted'.

--
Build a little, test a little.


GD said:
Thanks, Karl! I'm having a little trouble following your SQL (I'm pretty new
at this kind of query). Are Q & Q1 the standard designations for a make
table query's original and destination tables in SQL? Or do the actual table
names get used? And are you saying you want the new table to be called GD?

--
GD


KARL DEWEY said:
One way is to add an autonumber field, run a make-table query, and then
update the field based on Rank > 1.
This query will apply rank and make the new table ---
SELECT Q.NewField, Q.InvAmt, Q.[Inv#], Q.Auto, (SELECT COUNT(*) FROM [GD] Q1
WHERE Q1.[InvAmt] = Q.[InvAmt]
AND Q1.[Inv#] = Q.[Inv#]
AND Q1.[Auto] < Q.[Auto])+1 AS Rank INTO GD_Sorted
FROM GD AS Q
ORDER BY Q.InvAmt, Q.Auto;

--
Build a little, test a little.


GD said:
I forgot to mention that these duplicate records are often NOT consecutive,
as they appear in my example. Several records often separate these
duplications. Figured that might make a difference.

--
GD


:

I have a small table that has duplicate invoice entries that are supposed to
be there. What I need is to add a field that displays the invoice amount for
the 1st instance (i.e. earliest record) of the invoice # in the table, but
then lists 0.00 for all subsequent records using that invoice #:

Inv# InvAmt NewField
102214 7841.23 7841.23
102513 5619.6 5619.6
102513 5619.6 0.00
102739 8789.61 8789.61
102739 8789.61 0.00
102856 1300.49 1300.49
102856 1300.49 0.00
103027 2637.8 2637.8
103211 4226.75 4226.75
103366 4431.26 4431.26
103479 2139.87 2139.87
103603 3198.97 3198.97
103603 3198.97 0.00

Any query or SQL that might accomplish this?

THANKS!!!
Click to expand...
Click to expand...
Click to expand...
 
Top