A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dsum for running total



 
 
Thread Tools Display Modes
  #1  
Old February 23rd, 2006, 04:10 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 23rd, 2006, 04:38 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 23rd, 2006, 05:00 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 23rd, 2006, 08:41 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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  
Old February 23rd, 2006, 09:54 PM posted to microsoft.public.access.gettingstarted
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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 08:42 PM
Reporting subreport total on main report BobV Setting Up & Running Reports 22 November 1st, 2005 04:19 AM
Grand Total Problem PW11111 Running & Setting Up Queries 2 March 11th, 2005 06:44 PM
DSum Total patti New Users 1 February 17th, 2005 05:13 AM
Running Total Kristen Using Forms 3 June 28th, 2004 09:21 PM


All times are GMT +1. The time now is 01:24 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.