s_ling
asked on
temporarily file + loop
Hi,
I got a table with BLKNO, BLKAREA ....etc.
For example, this is the records that have been saved in the access table after the user key in.
BLKNO BLKAREA
1 79
1 79
1 79
2 71
2 71
3 60
3 60
3 60
3 60
4 71
4 71
--------------------------
4 281
--------------------------
with the same BLKNO, the BLKAREA must be the same too according to the first record that the user key in. For example, the BLKNO 1, BLKAREA must be 79.
Here I would like to create a temporarily file to sum the BLKNO and BLKAREA.
How can I write the 'loop' to sum the BLKNO and BLKAREA. For example, with the BLKNO 1 that user key in, the temp file will count 1. The other BLKNO 1 will not be count anymore. The same concept is using in BLKAREA.
I would like to know how to write the coding.
thanks!
I got a table with BLKNO, BLKAREA ....etc.
For example, this is the records that have been saved in the access table after the user key in.
BLKNO BLKAREA
1 79
1 79
1 79
2 71
2 71
3 60
3 60
3 60
3 60
4 71
4 71
--------------------------
4 281
--------------------------
with the same BLKNO, the BLKAREA must be the same too according to the first record that the user key in. For example, the BLKNO 1, BLKAREA must be 79.
Here I would like to create a temporarily file to sum the BLKNO and BLKAREA.
How can I write the 'loop' to sum the BLKNO and BLKAREA. For example, with the BLKNO 1 that user key in, the temp file will count 1. The other BLKNO 1 will not be count anymore. The same concept is using in BLKAREA.
I would like to know how to write the coding.
thanks!
ASKER
Hi acperkins,
I cannot sum all the BLKNO and BLKAREA. I need to count how many BLKNO that the user key in and count the sum for the BLKAREA of each different BLKNO
I cannot sum all the BLKNO and BLKAREA. I need to count how many BLKNO that the user key in and count the sum for the BLKAREA of each different BLKNO
ASKER
how to write the coding in vb?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi acperkins,
thanks!
is that possible to display the sum in the 2 textboxes automatically without pressing any button?
I tried to use it in the cmdSave button but a compile error "Sub, Function, or Property not defined (Error 35)" occurred.
thanks!
is that possible to display the sum in the 2 textboxes automatically without pressing any button?
I tried to use it in the cmdSave button but a compile error "Sub, Function, or Property not defined (Error 35)" occurred.
ASKER
besides, I need the looping so that the records won't be add for twice.
Not sure what you mean by "2 textboxes", but in order to use ADO you must first make a reference to it.
If this is not the problem, post your code and indicate the line that causes the problem.
If this is not the problem, post your code and indicate the line that causes the problem.
Ok let's try it one step at a time:
1. Start a new Project
2. Add a reference to ADO
3. Add 2 text boxes: Text1 and Text2 and a Command button: Command1
4. Add the following code to the form, making sure to change the reference to the database name.
Private Sub Command1_Click()
GetTotals
End Sub
Sub GetTotals()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Source = "Select Count(BLKNO) AS CountBLKNO, " & _
"Sum(BLKAREA) As SumBLKAREA " & _
"From (Select BLKNO, BLKAREA " & _
"From AccessTable " & _
"Group By BLKNO, BLKAREA)"
.ActiveConnection = "Provider=Microsoft.Jet.OL
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open Options:=adCmdText
Text1.Text = CStr(!CountBLKNO)
Text2.Text = CStr(!SumBLKAREA)
.Close
End With
Set rs = Nothing
End Sub
5. Run the code and press the Command1 button.
6. You should see 4 in Text1 and 281 in Text2
1. Start a new Project
2. Add a reference to ADO
3. Add 2 text boxes: Text1 and Text2 and a Command button: Command1
4. Add the following code to the form, making sure to change the reference to the database name.
Private Sub Command1_Click()
GetTotals
End Sub
Sub GetTotals()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Source = "Select Count(BLKNO) AS CountBLKNO, " & _
"Sum(BLKAREA) As SumBLKAREA " & _
"From (Select BLKNO, BLKAREA " & _
"From AccessTable " & _
"Group By BLKNO, BLKAREA)"
.ActiveConnection = "Provider=Microsoft.Jet.OL
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open Options:=adCmdText
Text1.Text = CStr(!CountBLKNO)
Text2.Text = CStr(!SumBLKAREA)
.Close
End With
Set rs = Nothing
End Sub
5. Run the code and press the Command1 button.
6. You should see 4 in Text1 and 281 in Text2
s_ling, you might need a query like:
SELECT DISTINCT count(BLKNO), sum(BLKAREA) FROM Table1 GROUP BY BLKNO, BLKAREA
in order to achieve the result you wish. Note the DISTINCT keyword.
Hope this helps.
SELECT DISTINCT count(BLKNO), sum(BLKAREA) FROM Table1 GROUP BY BLKNO, BLKAREA
in order to achieve the result you wish. Note the DISTINCT keyword.
Hope this helps.
ASKER
Thanks! acperkins
I get the answer! but I don't want the user to click the command button.
What I mean is after the user key in the BLKNO and BLKAREA and click Save button, the sum for the BLKNO and BLKAREA will display automatically in the textbox.
I get the answer! but I don't want the user to click the command button.
What I mean is after the user key in the BLKNO and BLKAREA and click Save button, the sum for the BLKNO and BLKAREA will display automatically in the textbox.
This was just and example. Call the GetTotals procedure when they click the Save button
ASKER
thanks!
Select Sum(BLKNO) As SumBLKNO,
Sum(BLKAREA) As SumBLKAREA
From AccessTable
Group By
BLKNO, BLKAREA