Get Counts

harpua · June 9th, 2003, 10:05 PM

Hello
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.
Mike

Blaise · June 9th, 2003, 10:28 PM

If you are having troubles with your joins, why not try building two seperate recordsets...

Code:

strSQLP = "SELECT DISTINCT Count(*) AS PENDING " _
        & "FROM Orders " _
        & "WHERE StoreNo = OrdersStoreNo " _
        & "AND Statis = 'Pending'"

strSQLF = "SELECT DISTINCT Count(*) AS FILLED" _
        & "FROM Orders " _
        & "WHERE StoreNo = OrdersStoreNo " _
        & "AND Statis = 'Filled'"

Another way under ASP would be like this...

Code:

strSQL = "SELECT DISTINCT * FROM " _
       & "WHERE StoreNo = OrdersStoreNo"

SET Rs = Server.CreateObject("AODODB.RecordSet")
Rs.Open strSQL, objConn

Dim Pending, Filled
Pending = 0
Filled = 0

WHILE NOT Rs.EOF
  IF Rs("Statis") = "Pending" THEN Pending = Pending + 1
  IF Rs("Statis") = "Filled" THEN Filled = Filled + 1
  Rs.MoveNext
WEND

IF NOT Rs.BOF THEN Rs.MoveFirst

I hope this helps or maybe gives you a few more ideas as to what your problem might be.

Cheers!

harpua · June 9th, 2003, 10:52 PM

Thanks for the ideas my brain feels better. I thought of the 2 recordsets but was hoping there was a more efficient way of doing it with one statement, I'll use your second suggestion, again thanks the help.

KenSchaefer · June 10th, 2003, 12:45 AM

You can do this in a single SQL statement. Just use:

SELECT
   StoreID,
   StoreName,
   OrderStatus,
   COUNT(*)
FROM
   Stores
INNER JOIN
   Orders
ON
   Stores.StoreID = Orders.StoreID
GROUP BY
   StoreID, StoreName, OrderStatus
ORDER BY
   StoreID, OrderStatus DESC

Cheers
Ken

www.adOpenStatic.com