Thread: Get Counts
View Single Post
  #1 (permalink)  
Old June 9th, 2003, 10:05 PM
harpua harpua is offline
Friend of Wrox
Join Date: Jun 2003
Location: Oklahoma City, Oklahoma, USA.
Posts: 249
Thanks: 0
Thanked 0 Times in 0 Posts
Default Get Counts

Can somebody tell me a way to get this to work. I have one table with my Store Name and ID's and I have another table for Orders and OrderStatis. This will either be Pending or Filled, they are joined together by the StoreID. What I need is for each store to know the Count for both Pending orders and Filled orders. The database is Access.

I have tried using a Union Count statement but get errors, I'm thinking I may need to use something else but cannot figure out what.

strSQL = "SELECT StoreID, StoreName FROM Stores " &_
            "UNION SELECT COUNT DISTINCT(OrderPending) AS Pending FROM Orders WHERE StoreNo = OrdersStoreNo AND Statis = 'Pending' " &_
            "UNION SELECT COUNT DISTINCT(OrderPending) AS Filled FROM Orders WHERE StoreNo = OrdersStoreNo AND Statis = 'Filled' ;"

Any help or guidance is greatly appreciated.