Solved

temporarily file + loop

Posted on 2001-08-08
12
218 Views
Last Modified: 2010-05-02
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
Comment
Question by:s_ling
  • 6
  • 5
12 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6366507
Try

Select Sum(BLKNO) As SumBLKNO,
       Sum(BLKAREA) As SumBLKAREA
From   AccessTable
Group By
       BLKNO, BLKAREA
0
 

Author Comment

by:s_ling
ID: 6366527
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
 

Author Comment

by:s_ling
ID: 6366540
how to write the coding in vb?
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 50 total points
ID: 6366603
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
 

Author Comment

by:s_ling
ID: 6366637
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
 

Author Comment

by:s_ling
ID: 6366647
besides, I need the looping so that the records won't be add for twice.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6366650
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6366754
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
 
LVL 9

Expert Comment

by:Valliappan AN
ID: 6366799
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
 

Author Comment

by:s_ling
ID: 6367186
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6368286
This was just and example.  Call the GetTotals procedure when they click the Save button
0
 

Author Comment

by:s_ling
ID: 6370527
thanks!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question