Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count/DCount? (2000)

    I have a table that holds net returns for mutual funds. I need to be able to choose a particular fund and chart out its net returns by occurences. For example, how many times did the return fall between 0 and 1, 1 and 2, 2 and 3, etc. I will take these occurences and put them in a chart.

    How do I count these occurences? Is this done in a query or in VB?

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    Use the partition function in you query:

    <pre>SELECT DISTINCTROW Partition([Return],0, 3, 1) AS Range,
    Count(YourTable.Return) AS Count
    FROM YourTable
    GROUP BY Partition([Return],0,3,1);</pre>

    Francois

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    Francois,
    I was unable to make the partition query work. The following is my code. So far, I am able to count >=0, but I am unable to figure out how to count a range.

    SELECT DISTINCTROW Count(tblNetRet.netret) AS CountOfnetret, tblFUND.name
    FROM tblNetRet INNER JOIN tblFUND ON tblNetRet.fid = tblFUND.fid
    WHERE (((tblNetRet.netret)>=0))
    GROUP BY tblFUND.name
    HAVING (((tblFUND.name)="4144 Partners LP"));

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    Can you attache the database or at least a database with the two tables and the query ?
    I'll try to have a look at it.
    Francois

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    I have tried attaching the test database which is 10 mb and then I zipped it and it won't attach. I keep getting a message from the lounge to try again later.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    Attached file may be max 100K.
    You can mail it to me (my e-mail is in my profile)
    Francois

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    The partition function didn't work if you have negative numbers in the field to partition.
    So I have to use another method.
    See the new qryCurve I have create.
    The To column can be deleted if you don't need it.

    Here is the query:

    SELECT DISTINCTROW Int([netret]) AS [From], Int([netret])+1 AS [To], Count([tblNetRet].[netret]) AS CountOfnetret, [tblFUND].[name]
    FROM tblNetRet INNER JOIN tblFUND ON [tblNetRet].[fid]=[tblFUND].[fid]
    GROUP BY Int([netret]), Int([netret])+1, [tblFUND].[name]
    HAVING (((tblFUND.name)="4144 Partners LP"));
    Francois

  8. #8
    Star Lounger
    Join Date
    Mar 2001
    Location
    USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count/DCount? (2000)

    Thanks Francois for all of your help. I need to study this query so that I understand it and can apply this method in future queries.

    Thanks again!!

Posting Permissions

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