SQL Count across two tables in Access 2003

Hi

I'm sure this is simple but I cant get it to work

Table 1 is called "Keywords" and contains the columns [Keyword ID], Keyword, [Start State], [End State] and Description.

Table 2 is called "Test Step Results" and contains many columns but the ones I care about are Keyword and Result.

I need to create a Select sql query that will be used to populate a form.

I want it to retrieve all records from Table 1 (i.e. Select * from Keywords).along with two extra counts PassedCount & FailedCount. These are worked out by ascertaining how many times the Keyword appears in the Test Step Results table with the Result = 'Passed' or 'Failed' respectively.

I got this far but Access 2003 gives me an error and I'm not sure that Where condition looks right.

SELECT Keywords.[Keyword ID], Keywords.Keyword, Keywords.[End State], Keywords.[Start State], Keywords.Description, Count(Result) AS PassedCount, [Test Step Results].Result, Count(Result) AS FailedCount
FROM Keywords INNER JOIN [Test Step Results] ON Keywords.Keyword = [Test Step Results].Keyword
WHERE ((([Test Step Results].Result)="Passed") AND (([Test Step Results].Result)='Failed'))
ORDER BY Keywords.Keyword;

Error Given by Access is:

"You have tried to execute a query that does not include the specified expression 'Keyword ID' as a part of an aggregate function."

HELP PLEASE!!
kush1981Asked:
Who is Participating?
 
kush1981Connect With a Mentor Author Commented:
took me a while but solved this on my own: below is the code:


SELECT Keywords.[Keyword ID], Keywords.Keyword, Keywords.[Start State], Keywords.[End State], Keywords.Description, SUM(IIf([Test Step Results].[Result] = 'Passed',1,0)) AS PCount, SUM(IIf([Test Step Results].[Result] = 'Failed',1,0)) AS FCount
FROM Keywords LEFT JOIN [Test Step Results] ON Keywords.Keyword = [Test Step Results].Keyword
GROUP BY Keywords.[Keyword ID], Keywords.Keyword, Keywords.[Start State], Keywords.[End State], Keywords.Description;
0
 
Helen FeddemaCommented:
It looks like Table 2 needs Keyword ID for purposes of linking.  Could you post the database, or a cut-down version of it?
0
 
kush1981Author Commented:
I could try but that might take a while as i remove sensitive information.

Currently the two tables are related (but referential integrity is not enforced) through a one to many (Keyword is Keywords table must be unique.) relationship using the Keyword field.

I'l post a mini database as soon as I can.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
kush1981Author Commented:
Example Database.

The Select Query should hopefully produce:

Keyword ID Keyword  PassedCount   FailedCount
1                Login               1                  1
2                Logout             1                  0 or Null
3                ViewAccount 0 or Null          0 or Null

I have left out Start state, End State and Description to keep things simple.
db1.mdb
0
 
kush1981Author Commented:
http://www.codingforums.com/showthread.php?t=98319 they use Sum and Case but I couldnt get this to work either:

SELECT
survey.surveyID AS "Survey ID",
survey.title AS "Title",
survey.memberID AS "Member ID",
sum(case when status='P' then 1 else 0 end) as Paused,
sum(case when status='C' then 1 else 0 end) as Completed,
sum(case when status is NULL then 1 else 0 end) as Incomplete,
FROM
survey inner join responses
ON survey.surveyID = responses.surveyID
group by
survey.surveyID,
survey.title,
survey.memberID
0
 
kush1981Author Commented:
None
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.