zero a field in duplicate rows

[Manny]

Hi,
I have a table with duplicate rows, which is fine, that's what i need to
display.
i have the same field 'balance' in all those rows.
My question is; how can i zero the 'balance' field after the first row, only
the first row should show an $$ value in that field, all duplicates should be
zeroed out.

Regards,
Manny

 

[Smartin]

Hi,
I have a table with duplicate rows, which is fine, that's what i need to
display.
i have the same field 'balance' in all those rows.
My question is; how can i zero the 'balance' field after the first row, only
the first row should show an $$ value in that field, all duplicates should be
zeroed out.

Regards,
Manny

Hi Manny,

Hmm, Just a thought... I question why you want to do this? Are you
adding up the balance, e.g. in a query someplace, and do not want to
count the duplicate entries?

Perhaps there is another way to solve the underlying problem rather than
modifying the table data.

 

[Manny]

Hi Smartin,
It is correct, i need to sum the balance.
Here is what i have. i need to export the table to excel, where i have a
pivot table extracting the data.
Obviously the data is incorrect because it is adding all duplicates, that in
fact they are not duplicates...they are the detail of invoices....

Regards,

 

[Smartin]

If you do not need to see all the duplicate detail rows then perhaps
create a query to SELECT DISTINCT rows, and base your export on that
instead. If the Balance field has the same value across what you
consider to be duplicate rows, this is a one step process.

 

[Manny]

Smartin,
That is the thing....i need to see all detail records when i drill down...

Regards,

 

[Smartin]

Alrighty then... sounds like I am back to square one! Unless someone
else has an idea I guess we are looking at zeroing out all but one
record in each set of duplicates, just like you said in the first place (^:

There are quite a few posts on the topic of deleting duplicate rows and,
optionally, preserving one row with duplicate sets. Here's a relatively
recent one (courtesy of John Spencer):

http://preview.tinyurl.com/296hkz

You can leverage this technique. In your case obviously you want to
UPDATE and not DELETE. The adjustment should be pretty easy to make.

FWIW I still don't like the idea of modifying the table data, although
your mileage may vary. One way to avoid this would be to create a SELECT
query that uses the two-query technique in the linked article, and
include a conditional field (ala IIf) to check whether the current
record is in the "keep" list. If it is, use the original balance amount.
If not, substitute zero. Again, base your export on the query.

Hope this all helps! Sorry I dragged you all the way around the block
only to get back to the starting point...

Smartin,
That is the thing....i need to see all detail records when i drill down...

Regards,