Results 1 to 5 of 5
  1. #1
    Xierxes is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    3

    Counting two fields in the same table and grouping by month

    Hi all,

    I have just started using Access again after a long lay off. I am sure that there is a simple solution to my problem however I cannot work it out even after searching at length (and even reading some books!)

    I have a table that stores competency achievment information, eg the title of the competency, planned date for completion and actual date for completion (it is quite similar to the Issue's template provided by MS).

    What I would like to build is a query that provides me the number of planned and completed competencies each month. For example,

    Code:
    Month | Completed | Planned
    Jan | 0 | 10
    Feb | 20 | 30
    Mar | 25 | 5
    I have got close, but not able to summarise as the example above. My SQL is attached below. I have tried a crosstab and a union query and got close but no cigar. The end result is to build a PivotChart that displays this information. The closest I have so far is [not the complete result]:



    Code:
    NoCompleted|Completed|NoPlanned|Planned
    0| |1|13 01 2010
    0| |1|16 12 2010
    0| |1|07 04 2011
    0| |1|15 04 2011
    4|22 03 2010|0| 
    1|25 05 2010|0| 
    1|14 07 2010|0| 
    4|18 01 2011|0| 
    1|01 02 2011|0|
    using:

    Code:
     
    SELECT Count(tblTraineeCompetencyLog.DateCompleted) AS NoCompleted, Count(tblTraineeCompetencyLog.DatePlanned) AS NoPlanned, tblTraineeCompetencyLog.DateCompleted, tblTraineeCompetencyLog.DatePlanned
    FROM tblTraineeCompetencyLog
    GROUP BY tblTraineeCompetencyLog.DateCompleted, tblTraineeCompetencyLog.DatePlanned;
    Thanks,
    Rob
    Last edited by Xierxes; 04-07-2011 at 12:36 PM. Reason: Changed title and added info

  2. #2
    William McKinley is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2010
    Posts
    59
    Have you tried using the totals function in the query builder? Group by month, count on the two fields.

  3. #3
    kennejd is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Mar 2011
    Posts
    188
    What are the fields in your table?

  4. #4
    Xierxes is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    3

    Table fileds

    Hi all,

    Thanks for the replies,

    The fields in the table are:.

    CompetencyID - FK to the Competencies Table
    TraineeID - FK to the Trainee Table
    DatePlanned
    DateCompleted
    Comments

    My PK is the combination of CompetencyID and TraineeID.

    I have kept reading around the web and I am now looking at a CalendarTable and joining the two to provide a grouping.

    Cheers
    Rob

  5. #5
    Xierxes is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2011
    Posts
    3

    Solved it!

    Hi all,

    Well it took me a few days but I managed to develop a solution to my problem.

    I created two separate queries - one that counted completed competencis and one that counted planned competencies. I then built a union query and added a null field so that I could return either completed or planned competencies and sorted by month. Works a treat and boss is very happy with results. For those interested the SQL for all queries is listed below.

    Code:
    SELECT Count(tblTraineeCompetencyLog.DatePlanned) AS Total, Format([DatePlanned],"mmm") AS [Month], Year([DatePlanned]) AS [Year]
    FROM tblTraineeCompetencyLog
    GROUP BY Format([DatePlanned],"mmm"), Year([DatePlanned])
    HAVING (((Count(tblTraineeCompetencyLog.DatePlanned))>0));
    Code:
    SELECT Count(tblTraineeCompetencyLog.DateCompleted) AS Total, Format([DateCompleted],"mmm") AS [Month], Year([DateCompleted]) AS [Year]
    FROM tblTraineeCompetencyLog
    GROUP BY Format([DateCompleted],"mmm"), Year([DateCompleted])
    HAVING (((Count(tblTraineeCompetencyLog.DateCompleted))>0));
    and finally the union query
    Code:
    Select Null As Planned, [Total]  As Completed,[Month],[Year] FROM qryCompetenciesA
    UNION ALL Select [Total],Null, [Month],[Year] As [Type] FROM qryCompetenciesB;
    Cheers
    Rob

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

Similar Threads

  1. Pivot Chart Data Labels
    By Rick West in forum Forms
    Replies: 5
    Last Post: 08-08-2013, 10:00 AM
  2. Pivot Chart help.
    By lorenambrose in forum Access
    Replies: 8
    Last Post: 02-16-2011, 12:02 PM
  3. Replies: 2
    Last Post: 07-12-2010, 05:39 AM
  4. Multiple Plot Pivot Chart
    By Catch Wrestler in forum Reports
    Replies: 0
    Last Post: 06-21-2010, 10:17 AM
  5. Creating a Pivot Chart....
    By spcalan in forum Access
    Replies: 0
    Last Post: 01-08-2009, 03:28 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