SQL Statement Count Total Quantity

[carlovfrimily]

Anyone has any idea on how to calculate the transaction date by model and then count out the total quantity for that transaction date for that day?
For example:-
I want to set a target 2th March 2005 starting to count for the transaction date and time.Then,it will pick out only the transaction date(trans_date) and time which i set in the database for me by following the certain model.Anyone can tell me how to write for the coding?Thank you.

[RobDog888]

Something like...

SELECT Count(trans_date) As Transactions FROM Table1 GROUP BY trans_date HAVING trans_date = '03/02/2005'

[carlovfrimily]

Thanks lot,Robdog

[RobDog888]

No prob. Glad to help

Ps, dont forget to Resolve your thread

[carlovfrimily]

May I know what's wrong with my coding below:-

sSQL = "SELECT StartDate FROM DateTime"
Set oRs = oSQL.Execute(sSQL)

sSQL = "insert into PackQty Select * from Model_Defined_View where trans_date >= '" & StartDate & "'"
Set oRs2 = oShip.Execute(sSQL)

Can the Set oRs in two diff SQL Statement be the same without setting it to oRs and oRs1?When,it debug,it couldnt get my StartDate.So,it pick up the irrelevant trans_date from Model_Defined_View also.

[flair]

It doesn't recognise StartDate without a reference to the recordset
Use:

sSQL = "SELECT StartDate FROM DateTime"
Set oRs = oSQL.Execute(sSQL)
 
sSQL = "insert into PackQty Select * from Model_Defined_View where trans_date >= '" &[B] oRs!StartDate [/B] & "'"
Set oRs2 = oShip.Execute(sSQL)

[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

[carlovfrimily]

May I know where is my mistakes?

[carlovfrimily]

Do Until rst.EOF

fg.Rows = rst.RecordCount + 1
fg.TextMatrix(Row, 0) = rst!Model
fg.TextMatrix(Row, 1) = rst!TargetQty
' Row = Row + 1

If fg.TextMatrix(Row, 0) <> ModelDefined Then 'How to write the coding to compare with the two row of ModelDefined instead of comparing only with the first row of ModelDefined here? Qty = 0
Else
Qty = oRs1!Qty
End If

TargetQty = rst!TargetQty
OnHand = rst!OnHand
sTotal = Qty + OnHand

fg.TextMatrix(Row, 2) = sTotal

If fg.TextMatrix(Row, 2) < TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbRed

ElseIf fg.TextMatrix(Row, 2) >= TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbGreen
End If

Row = Row + 1
rst.MoveNext

Loop

[carlovfrimily]

Sorrry for asking again and again here,but it is urgent!

[dglienna]

You want to compare to the first two rows of the flexgrid? You cannot compare to a record after you move to the next record.

if  fg.TextMatrix(Row, 0) <> fg.TextMatrix(1, 0) and   fg.TextMatrix(Row, 0) <> fg.TextMatrix(2, 0) then

if that's where they are, and you want to add to the quantity if it doesn't match either.

If you want something else, be specific about how it should work.

[carlovfrimily]

No,I mean I get my ModelDefined,TotalQty from the PackQty_View table in database.Then fg.TextMatrix(Row, 0) = Model which means this Model will compare with PackQty_View table to see if this Model exist in that or not,if not ,then set the Qty Packout is 0.

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

TargetQty = rst!TargetQty
OnHand = rst!OnHand
sTotal = Qty + OnHand

fg.TextMatrix(Row, 2) = sTotal

If fg.TextMatrix(Row, 2) < TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbRed

ElseIf fg.TextMatrix(Row, 2) >= TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbGreen
End If

[flair]

You must exit the loop once a match is found

Do Until rst.EOF
If fg.TextMatrix(Row, 0) <> ModelDefined Then
Qty = 0
Else
Qty = oRs1!Qty
Exit Do
End If
Row = Row + 1
rst.MoveNext
 
Loop

[carlovfrimily]

It works when i tried the following code,but the problems i faced now is it can't turn to green although the quantity is more than the TargetQty.
For example:- sTotal(20000) > TargetQty(20),but it still run the first line and get red instead of green.


....
Do Until rst.EOF
sSQL = "Select * from PackQty_View where ModelDefined='" & rst!Model & "'"
Set oRs1 = oShip.Execute(sSQL)

If oRs1.RecordCount <> 0 Then
sTotal = oRs1!Qty + rst!OnHand
Else
sTotal = rst!OnHand
End If

oRs1.Close


fg.Rows = rst.RecordCount + 1
fg.TextMatrix(Row, 0) = rst!Model 'Run Model column
fg.TextMatrix(Row, 1) = rst!TargetQty 'Run Target Quantity
fg.TextMatrix(Row, 2) = sTotal 'Run Total Quantity


If fg.TextMatrix(Row, 2) < rst!TargetQty Then 'fg.TextMatrix(Row, 2) TargetQty fg.TextMatrix(Row, 1)
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbRed

ElseIf fg.TextMatrix(Row, 2) >= rst!TargetQty Then
fg.Row = Row
fg.Col = 2
fg.CellBackColor = vbGreen
End If

Row = Row + 1
rst.MoveNext

Loop

[carlovfrimily]

Izit because i declare it as

Dim sTotal As Integer?So,it can't take it a large quantity?So,what should I declare it as?Pls help.Thanks

[dglienna]

integers are limited to 32768.

Long is the biggest, but you could also use currency, which holds larger numbers
I didn't realize the numbers were that big.

[carlovfrimily]

Oh,thanks lots lots to all of you.My program could finally work out.Juz that i need to make my flexgrid more attractive now.