Link to home
Create AccountLog in
Avatar of kush1981
kush1981

asked on

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!!
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

It looks like Table 2 needs Keyword ID for purposes of linking.  Could you post the database, or a cut-down version of it?
Avatar of kush1981
kush1981

ASKER

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.
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
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
ASKER CERTIFIED SOLUTION
Avatar of kush1981
kush1981

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
None