Make Table Query a

[Dutchie]

Hello



I am a rookie when it comes to access. I managed to get the problem working before but I lost the coding
have been browsing the internet for a few days but can't find what I am looking for that would help me out.
So hope y'all can help me out.

I have a linked table to a XLS on the computer. The linked table contains duplicates and I wan to generate a new table without the duplicates. Using the table generate query.

the xls sheet looks like this

Item ID	Item Description	Qty On Hand
123	222	10
123	222	0
123	222	0
456	555	0
456	555	0
789	888	5
789	888	0
789	888	0

The following is what I want to achieve

123	222	10
456	555	0
789	888	5

Basically that I keep in record and keep the one with the highest value or keep a QTY of 0 if all duplicate records are zero

I am puzzled and would appreciate y'alls help

Dutchie

[Dutchie]

Solved :-) http://dba.stackexchange.com/questio...in-access-2007

[lfpm062010]

This query should give you the highest value for any given "Item ID" and "Item Description".

SELECT TableXLS.[Item ID], TableXLS.[Item Description], Max(TableXLS.[Qty On Hand]) AS [MaxOfQty On Hand]
FROM TableXLS
GROUP BY TableXLS.[Item ID], TableXLS.[Item Description]
ORDER BY TableXLS.[Item ID], TableXLS.[Item Description];