Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Creating table for charting purposes

    I have a table tracking projects that includes the following fields:

    Project Number
    Function
    Due Date
    Completion Date

    I need to create several dashboard charts that show (by month), the number of accumulated projects due compared to the number created. I would like to have a record for each month (over several years) in the following format:

    [Function] [Month/Year] [Count of Due dates] [Count of Competion Dates]
    A Jan 2011 5 2
    B Jan 2011 3 0
    A Feb 2011 7 (2 more than Jan) 2 (no change)

    The determinating of completed projects is based on data being entered into the field. All other field are populated.

    Any thoughts/suggestions. The result (I hope) will be used to create line charts showing progress towards completing all tasks.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    You can create two separate Totals queries, then combine their results in a third query.

    The first would count([project numbers)] when grouped by Function and Month([Due Date]) and Year([due Date])
    The second would count([project numbers)] when grouped by Function and Month([Due Date]) and Year([due Date]) where [completion Date] is not null.

    Post back if you need help with actually creting these queries.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    Jan 2008
    Location
    Naperville, Illinois, United States
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    John, sorry for the late reply - I got off on some other projects for a bit. That is a great idea - I should be able to do that without too much effort (I'm still a novice at Access, but a dangerous one at that!). Thanks for the suggestion

Posting Permissions

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