Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Count across two tables in Access 2003

Posted on 2011-02-15
6
Medium Priority
?
455 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

610 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