469,165 Members | 2,223 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,165 developers. It's quick & easy.

Counts query access

35 32bit
Is it possible to have counts from two different tables in one query? I tried this and it worked, but I have duplicate rows with the same count numbers.
May 10 '10 #1
3 1364
NeoPa
32,167 Expert Mod 16PB
Yes it is. What are you trying to do? It's very possible that what you want is not supported but your question is very open.
May 10 '10 #2
rhonda6373
35 32bit
The business scenario is that I have multiple test cases for single template IDs. The template IDs are stored in one table and the test cases are stored in another table. The common field is the TemplateID.

I need a count of both the number of TemplateIDs as well as the total count of test cases in a single query.

I was attempting to get a Pivot table with both counts and that are also spliced by status.

I am open to any other solutions.
May 10 '10 #3
NeoPa
32,167 Expert Mod 16PB
I would create a GROUP BY query that has another GROUP BY query as a subquery (See Subqueries in SQL).

Something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Count([TemplateID] AS TemplateIDs
  2.        , Sum([TestCases]) AS AllTestCases
  3.  
  4. FROM
  5.     (
  6.     SELECT   [TemplateID]
  7.            , Count([TestCase]) AS TestCases
  8.     FROM     [tblTestCase]
  9.     GROUP BY [TemplateID]
  10.     )
I would create a GROUP BY query that has another GROUP BY query as a subquery (See Subqueries in SQL).

Something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   Count([TemplateID] AS TemplateIDs
  2.        , Sum([TestCases]) AS AllTestCases
  3.  
  4. FROM
  5.     (
  6.     SELECT   [TemplateID]
  7.            , Count([TestCase]) AS TestCases
  8.     FROM     [tblTestCase]
  9.     GROUP BY [TemplateID]
  10.     )
There is no GROUP BY clause in the main query as all records are grouped together.
May 11 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Cady Steldyn | last post: by
13 posts views Thread by IndianaJonesWB | last post: by
2 posts views Thread by P Adhia | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.