If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Keeping, but not counting, duplicate entries
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!!! -- GD |
#2
|
|||
|
|||
Keeping, but not counting, duplicate entries
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 "GD" wrote: 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!!! -- GD |
#3
|
|||
|
|||
Keeping, but not counting, duplicate entries
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" wrote: 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 "GD" wrote: 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!!! -- GD |
#4
|
|||
|
|||
Keeping, but not counting, duplicate entries
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" wrote: 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" wrote: 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 "GD" wrote: 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!!! -- GD |
#5
|
|||
|
|||
Keeping, but not counting, duplicate entries
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" wrote: 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" wrote: 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" wrote: 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 "GD" wrote: 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!!! -- GD |
Thread Tools | |
Display Modes | |
|
|