[RESOLVED] Another SQL Question

[manofsteel00]

I am trying to get a percentage of how many products have been counted by using SQL statements. I have one that returns how many have been counted (checked) and another for how many have not been counted (needToCheck). I get a datatypoe mismatch when I try to equate the long variables to the connection execution. I was wondering if any knew how I could get the numbers I want easily rather than looping through the table twice. I would like to just be able to divide checked by needToCheck and multiply by 100 and output it to a label to let people know how far along they have come. Any suggestions? Thank you.

Private Sub completed()
    Dim checked As Long
    Dim needToCheck As Long
    Dim sql As String
    
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    connection.Open
    
    sql = "SELECT Count(PROD) AS Expr1 " & _
              "From TOGO " & _
              "WHERE (((TOGO.CHECKED)=TRUE));"
    
    checked = connection.Execute(sql)
    
    sql = "SELECT Count(PROD) AS Expr1 " & _
              "From TOGO " & _
              "WHERE (((TOGO.CHECKED)=FALSE));"
 
    needstoCheck = connection.Execute(sql)
End Sub

[mikee_phil]

create a query on your database joining the two queriess and then
you can use select (expr1/expr2)*100 as product_percentage from query_name

[manofsteel00]

I'm not sure that I follow you. Is there no way I can get the number that the select statements return in the recordset?

[Venus]

You can group multiple select statements.

SELECT (SELECT Count(PROD) From TOGO WHERE (((TOGO.CHECKED)=TRUE))) AS Checked, (SELECT Count(PROD) AS Expr1 From TOGO WHERE (((TOGO.CHECKED)=FALSE))) AS Unchecked

That command does only 1 trip to the server and it's a matter now of taking the value of (Checked/Unchecked) * 100

You can even do it in one command in SQL including the math, I hope my parenthesis match up.

SELECT ((SELECT Count(PROD) From TOGO WHERE (((TOGO.CHECKED)=TRUE))) / (SELECT Count(PROD) AS Expr1 From TOGO WHERE (((TOGO.CHECKED)=FALSE)))) * 100 AS RESULT

Now, you get one field returned and I think you'd reference it by Recordset!Result, you'd have to convert it to a string. That works with SQL Server, I'm assuming it will work with Access.

For a tip, your tables should begin with a tb. As in, call it tbToGo because if you move off of Access into a SQL database, you now have stored procedures, views, and tables. It's easier to see tbToGo is a table, spToGo is a stored procedure, and vToGo is a view. I found it to be very useful.

[manofsteel00]

Thank you very much Venus; however, I would also like to know how to retrieve the information from the recordset. Whenever I execute the SQL statement the information will be stored in a recordset, and I need to get that information so I can output the percentage to a label.

[Venus]

Hmm... if I think I understand you'd do the following

Private Sub completed()
    Dim checked As Long
    Dim needToCheck As Long
    Dim sql As String
    Dim rs as new ADODB.Recordset ' Here
    
    Set connection = New ADODB.connection
    connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= G:\Finance\Inventory Control\CycleCount\DB\cycleCount.mdb"
    connection.Open
    
    sql = "SELECT (SELECT Count(PROD) From TOGO WHERE (((TOGO.CHECKED)=TRUE))) AS Checked" & _
 ", (SELECT Count(PROD) From TOGO WHERE (((TOGO.CHECKED)=FALSE))) AS Unchecked"
 
    Set rs = connection.Execute(sql)
 
    if RS.EOF Then
        ' We have no records.  Hmm....
        Msgbox("No records found")
        Exit Sub
    End if
 
    if rs!Unchecked = 0 Then ' Need to test if unchecked is 0
          LabelToGo.Text = "0" ' Else, in formula below get a divide by zero error
          Exit Sub
    End If
 
    ' At this point, rs!Checked holds the number of Checked and rs!Unchecked holds the Unchecked
 
    LabelToGo.Text = cStr((rs!Checked / rs!Unchecked) * 100) 
End Sub

That gives you a start I hope, and I hope my parenthesis all match up.

[Venus]

By the way, I edited my original code above and trimmed the fat off it.

[manofsteel00]

That will work, thank you very much Venus.

[Venus]

Sigh, I hope you were able to figure out I was incorrect on what the fields were called. We were doing two "AS" Statements... like
Select (Select formula1 as Expr1 from tb1) As Checked

I went on the Expr1 but it was superseded by the second As and renamed to Checked. It's rs!Checked and rs!Unchecked. I modified my code to correct a second time. *Sigh*. Hate when that happens but anyway good luck!

[salvelinus]

I think the original problem may have been expecting the SELECT statement to return a value, rather than a recordset, as Venus alluded to. I'd also second her/his practice of naming objects with a prefix - tblFoo, qryFoo, etc - to make it easier to see what you're dealing with in code.
I've had plenty of AAAGGGHHH!!! stories about dealing with legacy work where tables, fields, macros, queries, reports, etc, were all named the same thing, often with reserved words (Date being a common one). Don't do it.