Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
accessing twice a memo field in a recordset | Excel Programming | |||
ADO Recordset include Field names | Excel Programming | |||
Summarizing field quantities for specific records | Excel Worksheet Functions | |||
ADO cloned recordset doesn't return field names | Excel Programming | |||
DAO query/recordset returns with field names | Excel Programming |