Sum MSAccess field quantities in a recordset from MSExcel
 

What other info do you need? I would do it in Access if I knew how.
But I need to sum all the data in that field and then put that one
total number in a cell in Excel.


....this is just a snipet from my coding...

sMCode = "M"
str = "SELECT SUM(maint_time) AS TOTAL FROM AvgDays WHERE isitM = '" &
sMCode & "' AND curr_location < '" & cl & "' AND county < '" & cnty
& "' GROUP BY maint_time"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
lRecords = rs.RecordCount
MsgBox lRecords

this just gets me a total number of records that match that criteria
(9). I want the total of all the numbers in those 9 records.

?????

Mike wrote:
A little more info may help. Why dont you just make the query in Access?

"gab1972" wrote:

Sorry if the subject sounded confusing. Here's what I'm trying to
accomplish.

I have an Access database named AvgDays. In that recordset, I have
the following columns:
permit_num
county
isitD
isitM
dist_days
maint_days
curr_location

dist_days and main_days have a number of days. isitM could contain a
letter, "M". This means that not all of the records have an M in the
field.

What I want to accomplish:
Search every record in the recordset AvgDays where isitM = "M"...when
found, I want to continuously sum maint_days...so that it searches all
records and sums the total number of days. So if there are 100
records and say 75 have the letter M in isitM, I want to sum that
value, move to the next record, add that number, and so and so forth.

Is this possible? Any help would be GREATLY appreciated.
.

Sum MSAccess field quantities in a recordset from MSExcel
 

something like this im guessing
With rs
Do While (Not .EOF)
Range("A2").Value = Range("A2").Value +
..Fields("TOTAL").Value
.MoveNext
Loop
End With

"gab1972" wrote:

What other info do you need? I would do it in Access if I knew how.
But I need to sum all the data in that field and then put that one
total number in a cell in Excel.


....this is just a snipet from my coding...

sMCode = "M"
str = "SELECT SUM(maint_time) AS TOTAL FROM AvgDays WHERE isitM = '" &
sMCode & "' AND curr_location < '" & cl & "' AND county < '" & cnty
& "' GROUP BY maint_time"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
lRecords = rs.RecordCount
MsgBox lRecords

this just gets me a total number of records that match that criteria
(9). I want the total of all the numbers in those 9 records.

?????

Mike wrote:
A little more info may help. Why dont you just make the query in Access?

"gab1972" wrote:

Sorry if the subject sounded confusing. Here's what I'm trying to
accomplish.

I have an Access database named AvgDays. In that recordset, I have
the following columns:
permit_num
county
isitD
isitM
dist_days
maint_days
curr_location

dist_days and main_days have a number of days. isitM could contain a
letter, "M". This means that not all of the records have an M in the
field.

What I want to accomplish:
Search every record in the recordset AvgDays where isitM = "M"...when
found, I want to continuously sum maint_days...so that it searches all
records and sums the total number of days. So if there are 100
records and say 75 have the letter M in isitM, I want to sum that
value, move to the next record, add that number, and so and so forth.

Is this possible? Any help would be GREATLY appreciated.
.

.

Sum MSAccess field quantities in a recordset from MSExcel
 

Thanks! That's exactly what I was looking for!!

Mike wrote:
something like this im guessing
With rs
Do While (Not .EOF)
Range("A2").Value = Range("A2").Value +
.Fields("TOTAL").Value
.MoveNext
Loop
End With

"gab1972" wrote:

What other info do you need? I would do it in Access if I knew how.
But I need to sum all the data in that field and then put that one
total number in a cell in Excel.


....this is just a snipet from my coding...

sMCode = "M"
str = "SELECT SUM(maint_time) AS TOTAL FROM AvgDays WHERE isitM = '" &
sMCode & "' AND curr_location < '" & cl & "' AND county < '" & cnty
& "' GROUP BY maint_time"
rs.Open str, cn, adOpenKeyset, adLockOptimistic
lRecords = rs.RecordCount
MsgBox lRecords

this just gets me a total number of records that match that criteria
(9). I want the total of all the numbers in those 9 records.

?????

Mike wrote:
A little more info may help. Why dont you just make the query in Access?

"gab1972" wrote:

Sorry if the subject sounded confusing. Here's what I'm trying to
accomplish.

I have an Access database named AvgDays. In that recordset, I have
the following columns:
permit_num
county
isitD
isitM
dist_days
maint_days
curr_location

dist_days and main_days have a number of days. isitM could contain a
letter, "M". This means that not all of the records have an M in the
field.

What I want to accomplish:
Search every record in the recordset AvgDays where isitM = "M"...when
found, I want to continuously sum maint_days...so that it searches all
records and sums the total number of days. So if there are 100
records and say 75 have the letter M in isitM, I want to sum that
value, move to the next record, add that number, and so and so forth.

Is this possible? Any help would be GREATLY appreciated.
.

.