?
Solved

temporarily file + loop

Posted on 2001-08-08
12
Medium Priority
?
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 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