We help IT Professionals succeed at work.

Find Minimum date in Access query where the amount not 0

ar92ldm asked
Hello experts.  
I am pulling data thru MS Access 2003 from a series of SQL tables that are linked to Access.
The expected result would be the product type, minimum of date where amount <>0, and the sum of amount for the type.

I already try a group by query chaning the date column to "min" and the Amount column to sum and it gives me the right amount, but gives me the lowest date and not lowest date with an amount not = 0.

I tried DMin on date with type and amount as criteria but can't get that to work becuase was receiving a SQL error [MICROSOFT][ODBC SQL SERVER DRIVER][SQL SERVER]EACH GROUP BY EXPRESSION MUST CONTAIN AT LEAST ONE COLUMN REFERENCE.(#329)

Raw Data:

type      date            amount
15            1/1/08            $0
15            2/15/09            $5
15            3/31/09            $10
18            1/18/09            $10
18            1/27/09            $10
18            1/28/09            $10
18            1/15/10            $10

Expect results:

type      date            amount
15            2/15/09            $15
18            1/18/09            $40
Thank you.
Watch Question

SELECT Min([Date]), Sum(Amount) FROM myTable WHERE Amount<>0:

oops, forget that.
SELECT Type, Min([Date]), Sum(Amount) FROM myTable WHERE Amount<>0 GROUP BY Type;


that's it.  thanks for your response.

don't forget to close the question.
Top Expert 2010

This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.