SQL Count across two tables in Access 2003
Posted on 2011-02-15
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."