Solved

temporarily file + loop

Posted on 2001-08-08
12
215 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
 
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now