We help IT Professionals succeed at work.
Get Started

Find Minimum date in Access query where the amount not 0

ar92ldm
ar92ldm asked
on
1,151 Views
Last Modified: 2013-11-28
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:
This problem has been solved!
Unlock 1 Answer and 6 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE