We help IT Professionals succeed at work.

Find Minimum date in Access query where the amount not 0

ar92ldm
ar92ldm asked
on
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.
Comment
Watch Question

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

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

Author

Commented:
that's it.  thanks for your response.

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

Commented:
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.