• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 233
  • Last Modified:

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
0
radstad
Asked:
radstad
1 Solution
 
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
 
awdCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now