distinct query

[slimjen]

Hi all, using access 2003/sql server 2008 I have an access front end linking to a sql back end. Now I need to put a condition to a query and having trouble where to begin. My table consist of 3fields: a list of salestickets, a three digit code and an amount. This table is an weekly excel import. The salesticket needs to be unique but there are times when the excel sheet would have duplicate salesticket numbers with different three digit codes Ex. '56789 DRE' and '56789 CRE'. The users wants to import the salesticket with the largest amount. I was thinking I could use an if statement but the problem is I would not know the largest amount at any given time. Ex. '56789 DRE 300' and '56789 CRE 400'. In this case; I would import the one with 400. This excel sheet can consist of more than 50000 rows so scrubing manually before the import is not an option especially since its automated. I also need the code to be sql server friendly. I started a query but it doesn't work:

Select distinct salesticket, coop, amount
from tblSalesticket
where max(amount)

Some guidance please. Thank you

[Conceptz]

Your question seems similar to the one I posted earlier today. I was referred to this link (thanks pbaldy!):
http://www.baldyweb.com/LastValue.htm

[slimjen]

Yes; it's similar but the import table will be different with each import. So I tried the first query because it may be what I am looking for except I get an error: "You tried to execute a query that does not include the specified expression 'Division' as part of an aggregate function. I have three field and each is important. When I leave off one of the fields, the query returns part of results needed.

[CODE][SELECT MAX (tblSalesticket.Amount) as MAXAmount, tblSalesticket.Salesticket, tblSalesticket.Division
FROM tblSalesticket
Group by tblSalesticket.salesticket/CODE]

[Conceptz]

For the example in the link, you'll need both queries to get it to work...

Call this qry_max

SELECT MAX (tblSalesticket.Amount) as MAXAmount, tblSalesticket.Salesticket
FROM tblSalesticket
Group by tblSalesticket.salesticket

Make another query to retrieve all 3 fields:

Select tblSalesticket.* 
From tblSalesticket Inner Join qry_max on (tblSalesticket.Amount = qry_max.MAXAmount) AND (tblSalesticket.Salesticket = qry_max.Salesticket)

[slimjen]

Got it! Thanks for all your help.