Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    13

    Question Unanswered: running total.. pretty tricky

    I have this query that counts how many reports was created each week of types A, B and C.

    'qryTR:
    TRANSFORM Count(*) AS [Count]
    SELECT [qryTR_ILM].[OpenWeek]
    FROM qryTR_ILM
    WHERE ((([qryTR_ILM].[Project])=Project()))
    GROUP BY [qryTR_ILM].[OpenWeek]
    PIVOT [qryTR_ILM].[Rank];

    Gives this output:
    OpenWeek A B C

    2001/33 5 23
    2001/34 2 8
    2001/35 7 3 20

    What I would like to do is to create a running total that adds the number of reports that has been created up to this week.

    Something like this:

    2001/33 5 23
    2001/34 7 8
    2001/35 14 11 43

    I started trying with this:
    SELECT qryTR.OpenWeek, qryTR.A, qryTR.B, qryTR.C, (SELECT SUM(A) FROM qryTR As qryTR2 WHERE qryTR2.OpenWeek<=qryTR.OpenWeek) AS TotalA
    FROM qryTR;

    I get an error message saying that qryTR.OpenWeek isn't recognized as a valid field name or expression. Is this because i'm running this query on another query instead of on a table?

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: running total.. pretty tricky

    Originally posted by speten
    I have this query that counts how many reports was created each week of types A, B and C.

    'qryTR:
    TRANSFORM Count(*) AS [Count]
    SELECT [qryTR_ILM].[OpenWeek]
    FROM qryTR_ILM
    WHERE ((([qryTR_ILM].[Project])=Project()))
    GROUP BY [qryTR_ILM].[OpenWeek]
    PIVOT [qryTR_ILM].[Rank];

    Gives this output:
    OpenWeek A B C

    2001/33 5 23
    2001/34 2 8
    2001/35 7 3 20

    What I would like to do is to create a running total that adds the number of reports that has been created up to this week.

    Something like this:

    2001/33 5 23
    2001/34 7 8
    2001/35 14 11 43

    I started trying with this:
    SELECT qryTR.OpenWeek, qryTR.A, qryTR.B, qryTR.C, (SELECT SUM(A) FROM qryTR As qryTR2 WHERE qryTR2.OpenWeek<=qryTR.OpenWeek) AS TotalA
    FROM qryTR;

    I get an error message saying that qryTR.OpenWeek isn't recognized as a valid field name or expression. Is this because i'm running this query on another query instead of on a table?
    By no means do I have your answer, but have you tried to run this from the query design window? If I can get it to run there, I can usually get it to run elsewhere. Crosstabs are few and far between for me.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Mar 2004
    Posts
    13

    Re: running total.. pretty tricky

    Originally posted by basicmek
    By no means do I have your answer, but have you tried to run this from the query design window? If I can get it to run there, I can usually get it to run elsewhere. Crosstabs are few and far between for me.
    Yep, tried that but it doesn't work there either.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    see Rudy's fabulous theta join


    izy
    currently using SS 2008R2

Posting Permissions

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