Help with complex Access query

[snypa]

--------------------------------------------------------------------------------

Hi

I was wondering if someone could assist me in creating a query that will select a row with the highest amount(money) amount duplicate records.

This is how it is structured:

1. I have a BatchNumber, ItemNumber and Amount,
2. An item is identified by a combination of the first two columns above ie BacthNumber and ItemNumber
3. There can be duplicates of an item but they will have different Amounts

Needed:

I want to create a query that will look for duplicate items, select the one with the highest amount and then add to that amount. I have managed to create a query that will find the duplicate items and arrange them in a way that the item record with the highest amount always appears first for each of the respective duplicate items. I'm lost on how to select that amount and add to it

Could someone please help!!!

Thanx in advance

 

[Tom]

Select Max(Amount)
From Table
Where XXX = XXX

 

[cjard]

What do you mean by "add to it" ? Surely you'd be better off just combining all known amounts, then setting a primary key:

SELECT batchnumber, itemnumber, sum(amount) INTO tmpNewTable FROM orders GROUP BY batchnumber, itemnumber

DELETE FROM orders

SELECT * INTO orders FROM tmpNewTable

DROP TABLE tmpNewTable

UPDATE ORDERS set amount = amount + ? WHERE batchnumber = ? AND itemNumber = ?
(read the PQ link in my sig if you dont know why I put ? marks in this query)

Now set a PK on the batchnumber, itemnumber fields to disallow duplicates in future
-

If the duplicate items must stay, put them somewhere else; instead of using SUM above, use MAX so that just your orders table contains what you want to update, and archive the crap elsewhere