Thread: Get Counts
View Single Post
  #2 (permalink)  
Old June 9th, 2003, 10:28 PM
Blaise Blaise is offline
Authorized User
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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!