Difficult SQL QUERY

Experts:



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.

thanks
radstadAsked:
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

     UNION

     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;

0
 
Anita030598Commented:
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.
0
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.