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

 

[MSAccessRookie]

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

You need an aggregate query that will find the Max() for the amount, and then Group BY the remaining fields that you require

 

[snypa]

Thank you for the response I'm trying to get it to display only the row with the maximun value but I cant seem to get it right, any tips?

 

[jal]

SELECT BatchNumber, ItemNumber, MAX(Amount)
FROM Table1
GROUP BY BatchNumber, ItemNumber

However, if you need to pull the entire row then make it a little more complex:

SELECT T.* FROM Table1 as T
INNER JOIN
(
SELECT BatchNumber, ItemNumber, MAX(Amount)
FROM Table1
GROUP BY BatchNumber, ItemNumber
) as HighestAmounts
ON HighestAmounts.BatchNumber = T.BatchNumber
AND Highestamounts.ItemNumber = T.ItemNumber
AND HighestAmounts.Amount = T.Amount

 

[snypa]

Thank you for the help guys managed to use sorting in access to get the required results