SQL Statement Count Total Quantity

[carlovfrimily]

Thanks,Flair.

However,I encounter another problems then.I will display out all the model and total quantity in the datagrid by two columns.When the total quantity is less than the set TargetQty,it will display out vbRed and if the total quantity meet the TargetQty,it will display vbGreen.To get the Total quantity,Qty should add the Exceed Packout for that day as shown in the code below so that it can compared with the TargetQty.But,dunno why some of the data should be green,but it turns to Red because it doesn't add up my Qty.

Maybe my explanation is not good enough.Below attached is the coding that I do.Hope anyone here can solve the problem for me.Thank You.

Private Sub DisplayGrid ()
.....
 
fg.Clear
sSQL = "SELECT * FROM PackQty_View"
Set oRs1 = oShip.Execute(sSQL)
'Run FROM PackQty_View and get ModelDefined(Ex:- I730 and I90) and Qty by 2 and 11
 
  Do Until oRs1.EOF
    
        fg.Rows = oRs1.RecordCount + 1
        fg.TextMatrix(Row, 1) = oRs1!Qty
        Qty = oRs1!Qty
        ModelDefined = oRs1!ModelDefined
        oRs1.MoveNext
    Loop
 
DisplaySQL = "SELECT Model,TargetQty,Exceed FROM DataGrid"
Set rst = oSQL.Execute(DisplaySQL)
 
    fg.ColWidth(0) = 2900
    fg.ColWidth(1) = 1600
    
    fg.ColAlignment(0) = flexAlignLeftCenter
    fg.ColAlignment(1) = flexAlignLeftCenter
 
    Row = 0
    
    fg.TextMatrix(Row, 0) = "Model"
    fg.TextMatrix(Row, 1) = "Total Quantity"
    
    Row = Row + 1
    
Do Until rst.EOF
    
        fg.Rows = rst.RecordCount + 1
        fg.TextMatrix(Row, 0) = rst!Model
 
        DisplaySQL = "SELECT * FROM DataGrid"
        Set rst = oSQL.Execute(DisplaySQL)
        
        If fg.TextMatrix(Row, 0) <> oRs1!ModelDefined Then  'Izit i compare like this make it compare only with the Model in the first row in PackQty_View instead of comparing with the second row?
                Qty = 0
            Else
                fg.TextMatrix(Row, 1) = oRs1!Qty
                Qty = oRs1!Qty
                oRs1.MoveNext
            End If
 
        TargetQty = rst!TargetQty
        Exceed = rst!Exceed
        sTotal = Qty + Exceed
    
        fg.TextMatrix(Row, 1) = sTotal
 
        If fg.TextMatrix(Row, 1) < TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbRed
 
        ElseIf fg.TextMatrix(Row, 1) >= TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbGreen
        End If
 
        Row = Row + 1
        rst.MoveNext
 
 
    Loop
 
Exit Sub
 
End Sub

[szlamany]

Why use multiple SQL statements at all?

insert into PackQty
   Select * from Model_Defined_View
   where trans_date >= (SELECT StartDate FROM DateTime)

[carlovfrimily]

Yah,i've put this statement at the beginning of the statement.However,m still facing some problems for the count quantity in the statement which make the color(green,red) turns differently because of the wrong count

[carlovfrimily]

anyone can help?Thanks

[carlovfrimily]

How to correct this part?It only keep on comparing with the first ModelDefined.So,when the second ModelDefined is actually run the Qty= oRs1!Qty part,but it goes to Qty = 0.Please help.

If fg.TextMatrix(Row, 0) <> oRs1!ModelDefined Then
Qty = 0
Else
Qty = oRs1!Qty
End If

[flair]

You are doing a

Do Until rst.EOF

Then inside this you are again defining

DisplaySQL = "SELECT * FROM DataGrid"
        Set rst = oSQL.Execute(DisplaySQL)

This would reset the recordset. The .Movenext would no more be effective. Don't set it again.

[carlovfrimily]

How about this?

Private Sub DisplayGrid ()
i = 0
fg.Clear
    sSQL = "SELECT * FROM PackQty_View "
    Set oRs1 = oShip.Execute(sSQL)
    Qty = oRs1!Qty
    ModelDefined = oRs1!ModelDefined
 
   DisplaySQL = "SELECT Model,TargetQty,Exceed FROM DataGrid"
   Set rst = oSQL.Execute(DisplaySQL)
 
    fg.ColWidth(0) = 2900
    fg.ColWidth(1) = 1600
    
    fg.ColAlignment(0) = flexAlignLeftCenter
    fg.ColAlignment(1) = flexAlignLeftCenter
 
    Row = 0
    
    fg.TextMatrix(Row, 0) = "Model"
    fg.TextMatrix(Row, 1) = "Total Quantity"
    
    Row = Row + 1
    
Do Until rst.EOF
 
    fg.Rows = rst.RecordCount + 1
    fg.TextMatrix(Row, 0) = rst!Model
  
 
    If fg.TextMatrix(Row, 0) <> oRs1!ModelDefined Then
                Qty = 0
            Else
                Qty = oRs1!Qty
            End If
           
 
        TargetQty = rst!TargetQty
        Exceed = rst!Exceed
        sTotal = Qty + Exceed
 
        fg.TextMatrix(Row, 1) = sTotal
 
        If fg.TextMatrix(Row, 1) < TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbRed
 
        ElseIf fg.TextMatrix(Row, 1) >= TargetQty Then
         fg.Row = Row
         fg.Col = 1
         fg.CellBackColor = vbGreen
        End If
 
        Row = Row + 1
        rst.MoveNext
   
Loop
Exit Sub
End Sub