• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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!

 
0
s_ling
Asked:
s_ling
  • 6
  • 5
1 Solution
 
Anthony PerkinsCommented:
Try

Select Sum(BLKNO) As SumBLKNO,
       Sum(BLKAREA) As SumBLKAREA
From   AccessTable
Group By
       BLKNO, BLKAREA
0
 
s_lingAuthor Commented:
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
0
 
s_lingAuthor Commented:
how to write the coding in vb?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
Sorry, I overlooked that:

You need to change the SQL as follows:
Select Count(BLKNO) As CountBLKNO,
      Sum(BLKAREA) As SumBLKAREA
From   AccessTable
Group By
      BLKNO, BLKAREA

So your code in VB would be as follows:

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   AccessTable " & _
             "Group By BLKNO, BLKAREA"
   .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=YourMDB"
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open Options:=adCmdText
   Msgbox "BLKNO Count = " & CStr(!CountBLKNO) & vbCR & _
          "BLKAREA Sum = " & CStr(!SumBLKAREA)
   .Close
End With
Set rs = Nothing

End Sub
0
 
s_lingAuthor Commented:
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.
0
 
s_lingAuthor Commented:
besides, I need the looping so that the records won't be add for twice.
0
 
Anthony PerkinsCommented:
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.
0
 
Anthony PerkinsCommented:
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.OLEDB.4.0;Data Source=Your.MDB"
  .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
0
 
Valliappan ANSenior Tech ConsultantCommented:
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.
0
 
s_lingAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
This was just and example.  Call the GetTotals procedure when they click the Save button
0
 
s_lingAuthor Commented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now