Solved

SQL Count across two tables in Access 2003

Posted on 2011-02-15
6
447 Views
Last Modified: 2013-11-28
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!!
0
Comment
Question by:kush1981
  • 5
6 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34899624
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
 

Author Comment

by:kush1981
ID: 34899661
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
 

Author Comment

by:kush1981
ID: 34899787
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:kush1981
ID: 34899835
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
 

Accepted Solution

by:
kush1981 earned 0 total points
ID: 34905394
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
 

Author Closing Comment

by:kush1981
ID: 34941228
None
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Archiving Access table older than 6 months 9 39
Create macro from runcode 30 26
DCount Type Mismatch 2 22
SQL Syntax: How to force case sensitive query? 2 30
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question