Results 1 to 5 of 5
  1. #1
    maggioant is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7

    Using running total in query to create graphs

    I need to create a graph that shows the cumulative number of Open vs. Closed issues on a month-to-month basis in an issue tracking database.



    The data table has the fields [* Creation Date] [Closed Date] [* Status] [PSI #]

    I have created 2 queries (one for open issues and one for closed issues) that group by the two date columns:
    Yr: Year([* Creation Date]) & Mth: Month([* Creation Date])
    Yr: Year([Closed Date]) & Mth: Month([Closed Date])

    The third column is a critera of [* Status] for New or Closed.

    The fourth column is a Count of [PSI #] which becomes [CountOfPSI #] in the query.

    I tried to create a running total in the 5th column, but instread of giving me a running total, the query just multiplies the [CountOfPSI #] field by 3. I can't figure out why!

    This problematic 5th column is an expression:
    ClosedTotal: DSum([CountOfPSI #],"New_Count",[* Status]="New")

    Any ideas how I can make this 5th column a running total of the [CountOfPSI #] field? I'm listing the Query full SQL below for further reference:

    SELECT Year([* Creation Date]) AS Yr, Month([* Creation Date]) AS Mth, Data.[* Status], Count(Data.[PSI #]) AS [CountOfPSI #], DSum([CountOfPSI #],"New_Count",[* Status]="New") AS ClosedTotal
    FROM Data
    GROUP BY Year([* Creation Date]), Month([* Creation Date]), Data.[* Status]
    HAVING (((Data.[* Status])="New"))
    ORDER BY Data.[* Status];

  2. #2
    maggioant is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7

    Solved

    Issue has been solved:

    TotalClosed: DSum("[CountOfPSI #]","Total_Closed","[Mth]<=" & [Mth] & "")

  3. #3
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    How you have not had any issues up till now I don't know. Using symbols such as * in your field names is fraught with danger. Rename them straight away.

    David

  4. #4
    maggioant is offline Novice
    Windows XP Access 2003
    Join Date
    Oct 2009
    Posts
    7

    Re:

    Oh, I've had plenty of issues. The problem is that the database is being used as a reporting tool for an online (eRoom) database. The field names have to match up with the export format of this database

  5. #5
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Then I suggest you create a new query that contains all the fields in the above table and give them alias names that comply with naming conventions and unreserved words.

    Then use this query as the bases for all other queries, that way you will not fall foul of issues relating to naming conventions.

    For example:

    Field in table [* Creation Date]
    Field in query CreationDate:[* Creation Date]

    David

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 09-30-2009, 09:40 AM
  2. Creating a Daily Running Total Query
    By seraph in forum Queries
    Replies: 0
    Last Post: 08-15-2009, 12:11 AM
  3. Help with Graphs.
    By Rameez in forum Access
    Replies: 0
    Last Post: 06-29-2009, 01:41 AM
  4. Replies: 0
    Last Post: 06-21-2009, 01:29 PM
  5. not correct running total in group
    By cmk in forum Reports
    Replies: 1
    Last Post: 12-06-2006, 05:56 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums