If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dsum for running total
I have reviewed many of the posts on this board and I am still having an
issue with Dsum. I have a table that contains ID(autonumber),CUSIP,Memo,Shares For each Cusip there may be multiple records. I need to keep a running total of the shares for each cusip. I get the running total to work but I am having difficulty getting it to reset at each new cusip. This is relatively easy in Excel but I have too much data for Excel to handle. Please help! |
#2
|
|||
|
|||
Dsum for running total
What are you doing this on, a form or report? If this is a report, the
easiest answer may be to use the report's built-in running sum capabilities. To get it to reset on each CUSIP, you would group on CUSIP then set a textbox's Running Sum property to Over Group. Set the Control Source of the textbox to the CUSIP field and the textbox will automatically give you a running sum that resets for each CUSIP. -- Wayne Morgan MS Access MVP "DMG" wrote in message ... I have reviewed many of the posts on this board and I am still having an issue with Dsum. I have a table that contains ID(autonumber),CUSIP,Memo,Shares For each Cusip there may be multiple records. I need to keep a running total of the shares for each cusip. I get the running total to work but I am having difficulty getting it to reset at each new cusip. This is relatively easy in Excel but I have too much data for Excel to handle. Please help! |
#3
|
|||
|
|||
Dsum for running total
Wayne
Thank you for responding. I planned to do it in a query because the total needs to be used in subsequent calculations. "Wayne Morgan" wrote: What are you doing this on, a form or report? If this is a report, the easiest answer may be to use the report's built-in running sum capabilities. To get it to reset on each CUSIP, you would group on CUSIP then set a textbox's Running Sum property to Over Group. Set the Control Source of the textbox to the CUSIP field and the textbox will automatically give you a running sum that resets for each CUSIP. -- Wayne Morgan MS Access MVP "DMG" wrote in message ... I have reviewed many of the posts on this board and I am still having an issue with Dsum. I have a table that contains ID(autonumber),CUSIP,Memo,Shares For each Cusip there may be multiple records. I need to keep a running total of the shares for each cusip. I get the running total to work but I am having difficulty getting it to reset at each new cusip. This is relatively easy in Excel but I have too much data for Excel to handle. Please help! |
#4
|
|||
|
|||
Dsum for running total
To do this in the query, you would use as the criteria of the DSum()
statement the field the query is sorted on and the current value of CUSIP. Example: MyRunningSumSum("FieldToSum", "TableName", "SortedField = " & [SortedField] & " And CUSIP = '" & [CUSIP] & "'") This will sum all values in the field you choose to sum where the value in the field you are sorting on is less than or equal to the value in the current record and where CUSIP matches the current record. The syntax assumes the sorted field to be a number data type and the CUSIP field to be a text data type. If the sorted field is a date/time data type, the syntax would be: MyRunningSumSum("FieldToSum", "TableName", "SortedField = #" & [SortedField] & "# And CUSIP = '" & [CUSIP] & "'") Be aware, that if the combination of the SortedField and CUSIP isn't unique to each record, the running sum will give you the same sum for all records with that pair of values in the fields. If you are sorting on more than one field, you may need to include those other sorted fields in the Where part of the DSum() also. -- Wayne Morgan MS Access MVP "DMG" wrote in message news Wayne Thank you for responding. I planned to do it in a query because the total needs to be used in subsequent calculations. "Wayne Morgan" wrote: What are you doing this on, a form or report? If this is a report, the easiest answer may be to use the report's built-in running sum capabilities. To get it to reset on each CUSIP, you would group on CUSIP then set a textbox's Running Sum property to Over Group. Set the Control Source of the textbox to the CUSIP field and the textbox will automatically give you a running sum that resets for each CUSIP. -- Wayne Morgan MS Access MVP "DMG" wrote in message ... I have reviewed many of the posts on this board and I am still having an issue with Dsum. I have a table that contains ID(autonumber),CUSIP,Memo,Shares For each Cusip there may be multiple records. I need to keep a running total of the shares for each cusip. I get the running total to work but I am having difficulty getting it to reset at each new cusip. This is relatively easy in Excel but I have too much data for Excel to handle. Please help! |
#5
|
|||
|
|||
Dsum for running total
Wayne
That did the trick. Thank you very much for all your help. "Wayne Morgan" wrote: To do this in the query, you would use as the criteria of the DSum() statement the field the query is sorted on and the current value of CUSIP. Example: MyRunningSumSum("FieldToSum", "TableName", "SortedField = " & [SortedField] & " And CUSIP = '" & [CUSIP] & "'") This will sum all values in the field you choose to sum where the value in the field you are sorting on is less than or equal to the value in the current record and where CUSIP matches the current record. The syntax assumes the sorted field to be a number data type and the CUSIP field to be a text data type. If the sorted field is a date/time data type, the syntax would be: MyRunningSumSum("FieldToSum", "TableName", "SortedField = #" & [SortedField] & "# And CUSIP = '" & [CUSIP] & "'") Be aware, that if the combination of the SortedField and CUSIP isn't unique to each record, the running sum will give you the same sum for all records with that pair of values in the fields. If you are sorting on more than one field, you may need to include those other sorted fields in the Where part of the DSum() also. -- Wayne Morgan MS Access MVP "DMG" wrote in message news Wayne Thank you for responding. I planned to do it in a query because the total needs to be used in subsequent calculations. "Wayne Morgan" wrote: What are you doing this on, a form or report? If this is a report, the easiest answer may be to use the report's built-in running sum capabilities. To get it to reset on each CUSIP, you would group on CUSIP then set a textbox's Running Sum property to Over Group. Set the Control Source of the textbox to the CUSIP field and the textbox will automatically give you a running sum that resets for each CUSIP. -- Wayne Morgan MS Access MVP "DMG" wrote in message ... I have reviewed many of the posts on this board and I am still having an issue with Dsum. I have a table that contains ID(autonumber),CUSIP,Memo,Shares For each Cusip there may be multiple records. I need to keep a running total of the shares for each cusip. I get the running total to work but I am having difficulty getting it to reset at each new cusip. This is relatively easy in Excel but I have too much data for Excel to handle. Please help! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
matching the column b(sku)c(count)with A | sammc2 | Worksheet Functions | 0 | November 3rd, 2005 07:42 PM |
Reporting subreport total on main report | BobV | Setting Up & Running Reports | 22 | November 1st, 2005 03:19 AM |
Grand Total Problem | PW11111 | Running & Setting Up Queries | 2 | March 11th, 2005 05:44 PM |
DSum Total | patti | New Users | 1 | February 17th, 2005 04:13 AM |
Running Total | Kristen | Using Forms | 3 | June 28th, 2004 09:21 PM |