Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2014

    Unanswered: Access 2010 Query not returning all required records

    Hi there

    My Access 2010 query is not returning all required records. Basically, there are 123 skills mapped across 11 centres. Each time I do a report on each centre, it should come up with the full 123 skills and provide a rating against each. When I run it for the different centres, it comes up with a different number of skills but never the whole 123. See SQL below

    SELECT Table_CentreDetails.Centre, Table_CentreDetails.Director, Max(Table_EmployeeSkillsProfiles2.Rating) AS MaxOfRating, Table_RoleSkills.SkillName
    FROM Table_RoleSkills INNER JOIN (Table_EmployeeSkillsProfiles2 INNER JOIN (Table_CentreDetails INNER JOIN Table_EmployeeInformation ON Table_CentreDetails.Centre = Table_EmployeeInformation.[State Centre]) ON Table_EmployeeSkillsProfiles2.[Employee Name] = Table_EmployeeInformation.[Employee Name]) ON Table_RoleSkills.[Skill ID] = Table_EmployeeSkillsProfiles2.[Skill ID]
    GROUP BY Table_CentreDetails.Centre, Table_CentreDetails.Director, Table_RoleSkills.SkillName
    HAVING (((Table_CentreDetails.Centre)=[Select State Centre (eg 'NSW State Centre'; 'Victoria State Centre'; 'Creative Industries Innovation Centre';Defence Industry Innovation Centre; Remote Enterprise Centre; or 'Resources Technology Innovation Centre', etc)]) AND ((Max(Table_EmployeeSkillsProfiles2.Rating)) Like "?"))
    ORDER BY Max(Table_EmployeeSkillsProfiles2.Rating) DESC;

  2. #2
    Join Date
    Oct 2003
    Is it possible that you have duplicate skills and centres mapped? I.e. is the same skill mapped to a centre more than once? if so, your group by clause may be removing them.

    Do they all have a rating? Your query might be ignoring NULL values.

Posting Permissions

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