Solved

SQL Count across two tables in Access 2003

Posted on 2011-02-15
6
445 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

914 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now