Delete duplicates, but update another field before that

[bigtwig]

I have a query that has some duplicate records in it (duplicate PO numbers). I have some invoices that have two or more checks cut against these duplicate POs.

What I want to do is to total the amount of the invoices against the PO, sum them together and place the total in a field (new or old, doesn't matter), and then delete the duplicate PO numbers.

Example of data:
PONum InvAmt
0001 10.00
0002 25.00
0002 45.00
0003 50.00

What I want is:
0001 10.00
0002 70.00
0003 50.00

Any suggestions?
Thanks,
Mike

 

[Darrylles]

Hi big,

Paste you're query.

It'll be easier (for you to use also - when solution posted).

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[bigtwig]

The field with the duplicates is PONum. If PONum is a duplicate, I want a sum of all the AccAmt and StdCost values stored. Once I have the sum, I want to delete the duplicate PONums and store the sums in the PONum record I'm keeping.

SELECT [COMBINED].PONum, [COMBINED].StdCost, [Vendor Allowances].AccAmt
FROM [COMBINED] LEFT JOIN [Vendor Allowances] ON [COMBINED].PONum = [Vendor Allowances].PONum
ORDER BY [COMBINED].PONum;