Update Query to delete some fields information

[maindaco via AccessMonster.com]

I have a list with following information:

Invoice Item InvAmount
10514 1 $3,210.00
10514 2 $3,210.00
10514 5 $3,210.00
20321 1 $1,630.00
20321 2 $1,630.00
30467 1 $5,340.00
50664 1 $890.00
50664 3 $890.00
50664 4 $890.00
50664 6 $890.00

As you can see, Invoice# can repeat since this list details the Items on an
invoice that are non taxable. Besides, the Inv Amount is the total amount of
the invoice, not the item amount. Then, what I need to do is to run an Update
Query that will leave only the first amount listed for an invoice and change
the repeated amount to 0. I still need the list to display the invoice number
(even repeated) and the item. The result should be something like the
following:

Invoice Item InvAmount
10514 1 $3,210.00
10514 2 $-
10514 5 $-
20321 1 $1,630.00
20321 2 $-
30467 1 $5,340.00
50664 1 $890.00
50664 3 $-
50664 4 $-
50664 6 $-

Any help will be really appreciated.

 

[Van T. Dinh]

Your sample may not be representative and repost if the suggestion doesn't
apply in this case.

If you want the InvAmount of Item = 1 to remain and wipe out the rest, the
SQL String should be something like:

UPDATE [YourTable]
SET InvAmount = Null
WHERE Item > 1

 

[maindaco via AccessMonster.com]

Van,

Thanks so much for your reply but I'm afraid it would not work. I forgot to
add 2 variables, first is that I'd like to remain the first item when Invoice
number is repeated and second is that first item may not be always = 1, it
should work with the first item listed when sorted ascending.

Your help on this will be really appreciated.

Manny.

 

[Van T. Dinh]

* I am not sure what you described in the first "variable". Did you mean
you want to retain the first amount?

* For the second "variable", I was guessing that it was possible and hence
the condition on my first post.

* Please re-post with more explanation of the first "variable" with a more
reprsentative sample set of data and the result you want from the sample
set.

 

[maindaco via AccessMonster.com]

Van,

Thanks so much for taking care of this. Let me explain you why I need this,
probably you can recommend a better solution.

I have a list of Invoices which have a break down by taxable line items only
and the total amount of the invoice (not the specific item amount - see table
A -). The thing is that Invoice # can be repeated since there would be
several line items referenced. Now, what I need to do to is keep only the
InvAmount for the first item listed in a group of repeated invoice numbers
and leave the rest blank or 0 (table B)

A B
Invoice    Item InvAmount Invoice    Item InvAmount
10514 1 $3,210.00 10514 1 $3,210.00
10514 2 $3,210.00 10514 2 $0.00
10514 5 $3,210.00 ========> 10514 5 $0.00
20321 2 $1,630.00 20321 2 $1,630.00
50664 5 $890.00 50664 5 $890.00
50664 6 $890.00 50664 6 $0.00

Now, I don't think the condition you proposed would work since it is based on
the supposition that "Item > 1", but this is not always true. What I need is
to mantain the amount of the first item listed in a group of repeated
invoices #.

I need that done because I will use that table to perform a Pivot Table in
Excel wich will count the Line Items involved and Summarize the Invoice
Amount, but if it is repeated it will be multiplied.

I will really appreciate your help on this. Thanks in advance.

 

[Van T. Dinh]

Like I wrote in my original reply, you first sample set set is not
representative enough & the suggestion based on the sample.

For this to work where the first item number may be something other than 1,
you neend a more complex SQL. Try (untested):

UPDATE [YourTable] As Main
SET InvAmount = 0
WHERE [Item] <>
(
SELECT Min(Sub.[Item]) As MinItemNo
FROM [YourTable] As Sub
WHERE Sub.Invoice = Main.Invoice
)

 

[maindaco via AccessMonster.com]

Hi Van,

It took me a little bit to have a chance to test the SQL and reply back to
you, but let me tell you that it worked fine. Great work buddy.

Now I have found a new dilemma, how can I make this work when I have an Item
repeated for the same Invoice #?

For example

Invoice Item Amount
10514 1 $3,210.00
10514 1 $3,210.00
10514 2 $3,210.00

My need here is the same, I would like to keep only the first item listed
even when it is not always = 1.
Finally one extra question, can I add the new SQL to the old one? How?

Thanks so much for your help.

 

[Van T. Dinh]

This one is a bit harder ...

There is no inherent sort order in Records store in the Table so there is no
way of telling which one is first or which one is second in the first 2 rows
of the posted sample.

If you have any other Field you can use to impose an (artificial) order, use
method similar to what I post.

If you don't, you probably have to do a SELECT DISTINCT Query and then use
this Query as the source for another Query similar to what I posted
previously. It can be combine into one Query but I leave it for you to try
....