troubleshooting Question

create query that returns the customers that had at least 50000 swing, Pos or Neg, between two years

Avatar of UniqueData
UniqueDataFlag for United States of America asked on
DatabasesMicrosoft AccessSQL
2 Comments1 Solution211 ViewsLast Modified:
I have a customer base that changes constantly. I would like to see the customers that had at least a $50,000 swing in business between last year and this. So the customer would have done business with us in 2006 and 2007 and has either produced $50,000 more between these two years or Produced less between the two years. Here is the SQL statement I have now.
SELECT qry2006Customers.CUCODE, qry2006Customers.CUNAME, Sum(qry2006Customers.LOADS) AS LoadCount, Sum(qry2006Customers.REVENUE) AS EstRev, Sum(([Loads]*Abs([PUYear]=2006))) AS Loads2006, Sum((Revenue*Abs([PUYear]=2006))) AS Revenue2006, Sum(([Loads]*Abs([PUYear]=2007))) AS Loads2007, Sum((Revenue*Abs([PUYear]=2007))) AS Revenue2007 INTO tblCustomerRevenue
FROM qry2006Customers
GROUP BY qry2006Customers.CUCODE, qry2006Customers.CUNAME
HAVING (((Sum(([Revenue]*Abs([PUYear]=2006))))>50000)) OR (((Sum(([Revenue]*Abs([PUYear]=2007))))>50000))
ORDER BY qry2006Customers.CUCODE;

any help would be wonderful.

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros