Results 1 to 7 of 7

Thread: DSum Help

  1. #1
    Join Date
    Jun 2012
    Posts
    85

    Unanswered: DSum Help

    I have been having problems with DSum, for some reason i cant get it to work for me. I have posted about this before but I cant get any of the tips/solutions to work.. This is my query...
    Name Return Run. Sum [What i want]
    aaa 0.1 #error [1100]
    aaa 0.2 #error [1300]
    aaa 0.3 #error [1600]
    aaa 0.1 #error [1700]
    bbb 0.5 #error [1500]
    bbb 0.4 #error [1900]
    bbb 0.1 #error [2000]
    bbb 0.3 #error [2300]
    bbb 0.2 #error [2500]

    Here is my expression... Format(1000+(DSum("Return","Monthly Performance Dates"," [Fund Name]<=" & [Fund Name] & "")*1000),"00.00")....

    I want to have the Run Sum, to restart each time a new name comes up... I need it to start at 1000 hence the formula...

    I have to use this in an area graph for a report, and in the Pivot chart view of a query. It is a simple query. I have tried using Dlookup etc. from the previous help others have said on this forum. If anyone can help please let me know...

  2. #2
    Join Date
    Feb 2008
    Posts
    1
    [Fund Name] sounds like it would be a text field strong the name of each fund. If this is the case I don't know why you would be using <= as your comparison operator. Also, assuming it's a text field, you're missing string delimiters and you need brackets around your table/query name since it has spaces. Your DSum should look more like;

    DSum("Return","[Monthly Performance Dates]"," [Fund Name]=""" & [Fund Name] & """")

    Try to get the DSum working on its own, then add back in the other parts of your expression.

  3. #3
    Join Date
    Jun 2012
    Posts
    85
    That doesnt work. With my expression, i get a running sum, while with your correction i dont. I just get the same number running down the column. I understand why i shouldnt be using <= but for sum reason it works. I tried to manipulate your expression but still for somereason i cant get the running some to restart for each fund.. Thanks for the input

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it could well be a data error
    because you have used spaces in your column/table names you will have to use square brackets to delimit those nam,es that have spaces in. its a bad habit to get into so in future don't
    use either
    capitalisation\CamelCase, eg MonthlyPerformanceDates
    OR
    Underscore eg: Monthly_Performance_Dates

    if fund name is string then it should be quoted to demonstrate to the compiler where the value to compare stops and starts

    where is the value for <=" & [Fund Name] coming from?
    as in
    Code:
    [Fund Name]<=" & [Fund Name] & "")*1000),"00.00")
    have you checked to make certain there is 'sane' value for [Fund Name]
    that should read
    Code:
    [Fund Name]<='" & [Fund Name] & "'")
    right now Beetles advice of forget the rest of the numeric expression for now, just prove the dsum is giving you the correct values

    can we see the actual values passed to the SQL engine. ie set a break point on the DSUM statement and examine the values.. make certain the

    what do you expect to do if DSUM returns a null value, as it will IF it cannot find a match for DSUM
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2012
    Posts
    85
    Ive never used code before in access. Ive only just started developing databases etc. still on the process of learning. I tried manipulating the data by inputid the ID of ech return.

    IDName Return Run. Sum [What i want]
    1 aaa 0.1 #error [1100]
    2 aaa 0.2 #error [1300]
    3 aaa 0.3 #error [1600]
    4 aaa 0.1 #error [1700]
    5 bbb 0.5 #error [1500]
    6 bbb 0.4 #error [1900]
    7 bbb 0.1 #error [2000]
    8 bbb 0.3 #error [2300]
    9 bbb 0.2 #error [2500]

    My expression is now... (DSum("Return","Monthly Performance Dates"," [ID]<=" & [ID] & "")

    Thanks for the advice with table names etc. i will keep that in mind! and That expression is give me a running total for all returns. I have over 3000 or so. but 55 different funds. Any other ideas for the running total to restarts for each fund?

    Here is the code in SQL View:

    SELECT [Monthly Performance Dates].ID, [Monthly Performance Dates].[Fund Name], [Monthly Performance Dates].Year, [Monthly Performance Dates].Return, Format(1000+(DSum("Return","Monthly Performance Dates"," [ID]<=" & [ID] & "")*1000),"00.00") AS RunTot
    FROM [Monthly Performance Dates]
    GROUP BY [Monthly Performance Dates].ID, [Monthly Performance Dates].[Fund Name], [Monthly Performance Dates].Year, [Monthly Performance Dates].Return;
    Last edited by Mike02; 06-11-12 at 13:06.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    If ID is an autonumber column, then each row will be returned. if you want a sum then it needs to be a sum on somethign that has more than one value int he table. from your sample I'd suspect that should be Name

    where are you using htis information?
    you may be better off doing this in a query using the sum function and an appropriate grouping

    eg
    select Name, Return, sum(wahtever) from mytable
    group by name
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2012
    Posts
    85
    I have the information in a query right now, I then am making a report on it. I have two chart/graphs i have made in my report one being a line analysis with the return, the other a growth chart using the area chart. I was able to get a running total but of the entire data. I still havent been able to get the running total for each fund. but i have been able to get it to get a running total for all the funds.
    Thanks for all the help so far!

    Mike
    ________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •