How do I update and delete at the same time

[rouxbox]

This is doing my head in as a newbie to Access

I have a QuoteItems table that has a [Item] and [Qty] field and gets
populated automatically from another source.

The above causes Item duplicates in the table. What I need is some help on a
query that will
Add all the [Qty]'s of the duplicate [Item]'s to a single [Item] with a sum
of all the duplicate [Qty]

e.g

Item Qty
Chair 10
Table 5
Chair 2

will result in

Item Qty
Chair 12
Table 5

 

[David Cox]

The real problem lies with the update from the other source. Is it not
possible to change that?

 

[rouxbox]

The real problem lies with the update from the other source. Is it not
possible to change that?

This is doing my head in as a newbie to Access

[quoted text clipped - 20 lines]

Chair 12
Table 5

No, unfortunately not. What I thought about is doing this in 2 steps
1. Agregate the SUM's of all the duplicate items , thus
Chair 12
Table 5
Chair 12

2. Run another query to delete uplicates

But How??

 

[David Cox]

The real problem lies with the update from the other source. Is it not
possible to change that?

This is doing my head in as a newbie to Access

[quoted text clipped - 20 lines]

Chair 12
Table 5

No, unfortunately not. What I thought about is doing this in 2 steps
1. Agregate the SUM's of all the duplicate items , thus
Chair 12
Table 5
Chair 12

2. Run another query to delete uplicates

But How??

Sorry, but I think this is a case where I won't help, but back away erasing
fingerprints. This approach is possible, but IMO, just begging for trouble.

 

[Neil Sunderland]

I have a QuoteItems table that has a [Item] and [Qty] field and gets
populated automatically from another source.

The above causes Item duplicates in the table. What I need is some help on a
query that will
Add all the [Qty]'s of the duplicate [Item]'s to a single [Item] with a sum
of all the duplicate [Qty]

Item Qty
Chair 10
Table 5
Chair 2

will result in

Item Qty
Chair 12
Table 5

Unless I'm missing something obvious, this will do it:
SELECT
Item, SUM(Qty) AS Quantity
GROUP BY
Item

 

[rouxbox via AccessMonster.com]

I have a QuoteItems table that has a [Item] and [Qty] field and gets
populated automatically from another source.

[quoted text clipped - 14 lines]

Chair 12
Table 5

Unless I'm missing something obvious, this will do it:
SELECT
Item, SUM(Qty) AS Quantity
GROUP BY
Item

Hi Niel
thanks for the post, but remember, I only want to sum the items Qty that are
duplicates (the same).

So, in my example the query should somehow sum the chairs 10 + 2 = 12 and
table remains 5

Hope it makes sense

 

[John Spencer]

How does the suggested solution not work? What result does it give that is
wrong.?

I guess you could do something like the following if you want to see sums
only for items that appear more than one time in the table.

SELECT Item, Sum(Qty) as SumQuantity
FROM YourTable
GROUP BY Item
HAVING Count(Item) > 1

I have a QuoteItems table that has a [Item] and [Qty] field and gets
populated automatically from another source.

[quoted text clipped - 14 lines]

Chair 12
Table 5

Unless I'm missing something obvious, this will do it:
SELECT
Item, SUM(Qty) AS Quantity
GROUP BY
Item

Hi Niel
thanks for the post, but remember, I only want to sum the items Qty that
are
duplicates (the same).

So, in my example the query should somehow sum the chairs 10 + 2 = 12 and
table remains 5

Hope it makes sense