Difficult SQL QUERY


I have to make a query for table in Access , like this:

City            Orders
A            200
B            500
A            1200
A            700
B            900
C            400

Using one or two query to reach result like this:

City             Orders<=500      Orders>500
A             1                2
B             1             1
C             1                  

Please answer as soon as possible.

Who is Participating?
awdConnect With a Mentor Commented:
Here is ONE solution to your problem.  Just past the text of the 2 queries into the SQL design screen.  This assumes that your table is named "tblOrders", col 1 is "City", col 2 is "Orders".

"qrySplit" is as follows:

     SELECT City, Count(*) AS "Orders <= 500", 0 AS "Orders > 500"
     FROM tblOrders
     WHERE tblOrders.Orders <= 500
     GROUP By City


     SELECT City,  0, Count(*)
     FROM tblOrders
     WHERE tblOrders.Orders > 500
     GROUP By City;
qryCombine is as follows:

     SELECT qrySplit.City, SUM(qrySplit.["Orders <= 500"]), SUM(qrySplit.["Orders > 500"])
     FROM qrySplit
     GROUP BY qrySplit.City;

I remember reading the exact same question in MS Access section like yesterday or the day before. Did you post it there as well?
But I see that you registered with EE just today! Have you searched it in MS Access Section.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.