Accessing SUM results from a SELECT query 
Author Message
 Accessing SUM results from a SELECT query

Hi,

How do I get access to the SUM() results in a query run via a recordset?

- Eddy



Sat, 02 Sep 2000 03:00:00 GMT  
 Accessing SUM results from a SELECT query

Use a SQL statement like the following:

SELECT Sum(Orders.Freight) AS TotalFreight FROM Orders;

When you create a Recordset based on this statement, you can access the
column 'TotalFreight' just as you would any other column in a Recordset.

For example:

Dim Rs As Recordset
Dim sSQL as String

sSQL = "SELECT Sum(Orders.Freight) AS TotalFreight FROM Orders;"

Set Rs = OpenRecordset(sSQL)

TotalFreight is now available in Rs.Fields("TotalFreight").Value. Or if you
prefer, Rs("TotalFreight") or Rs!TotalFreight.

I hope this helps.

David
---
David S. Pendleton
in-process software, inc.



Sun, 03 Sep 2000 03:00:00 GMT  
 Accessing SUM results from a SELECT query

Eddy,
     Here are 2 ways:

Set rstSampleTable = dbSampleDatabase.OpenRecordset("Select Sum(Weight) As
Tot_Weight from SampleTable where Status = 'A'", dbOpenSnapshot)
If Not rstSampleTable.EOF Then
    rstSampleTable("Tot_Weight") (This contains your sum.)
End If
rstSampleTable.Close
Set rstSampleTable = Nothing

NOTE:  The above sample code is for an Access database.  If you're using a
SQL-type database (like Oracle), you need to modify the SELECT statement
("Select Sum(Weight) Tot_Weight from..."), as the databases treat column
aliases differently.

The 2nd way:

Set rstSampleTable = dbSampleDatabase.OpenRecordset("Select Sum(Weight)
from SampleTable where Status = 'A'", dbOpenSnapshot)
If Not rstSampleTable.EOF Then
    rstSampleTable.Fields(0) (This contains your sum.)
End If
rstSampleTable.Close
Set rstSampleTable = Nothing

Hope it helps,
Bob
--
Bob Uyeda, Executive Vice President
Prestige Software, Inc.

Phone: (714) 283-2323, x141; Fax: (714) 283-1029
"Honest, reliable, custom software development.  On-time and on-budget or
your money back!"
Visit us at http://www.prestige-software.com



Quote:
> Hi,

> How do I get access to the SUM() results in a query run via a recordset?

> - Eddy



Tue, 12 Sep 2000 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Populating a text box with the results of a select query

2. Creating a Column Collection from Select Query's Result

3. SELECT queries - Use Access query or not?

4. accessing query results

5. Access query results from VBA

6. Exporting queries results from Access to Excel

7. Pasting Access Query results to a e-mail.

8. import query results from Access

9. Different query results in Access 2000 than in VB

10. ??? Result of a Query into different Access 2.0 database

11. VB ComboBox filled with Access Query Results

12. LIKE query gives different results in Access 2000 and VB6 SP5

 

 
Powered by phpBB® Forum Software