help Selecting distinct records 1

[rk33]

hello,
Here is my query.
select 'MNOP' as [Workstream],
count([Functional Specs]) as [Total Functional Specifications] ,
count([Test Case]) as [Total Functional Specifications Covered ]
from
(Select distinct r.requirement_name as [Functional Specs],
tt.[Test Name] as [Test Case]
from
requirementinfo r
left outer join
(select * from trace t
inner join TestPlan Tp
on t.trace_element_name = tp.[Test Name]) as tt
on r.requirement_id = tt.root_element_id)

Now the raw data is returned is
FunctionalSpec Test case
ABC
DEF MNO
ADK
ADM BOL
ADM BMD

But as per the query i get a distinct count of 5 and it should show only 4. Is there a way to work this out in access?
Help needed asap.
Please assist.
Thanks,

 

[lespaul]

Maybe if you provide some sample data from all the tables involved and your expected results vs. actual results we could help you figure out why your query isn't working as expected.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[rk33]

The sample data
Requirementinfo Table
Requirement_name
ABC
DEF
ADK
ADM

Trace table
trace element name Requirementname tracedto
MNO DEF
BOL ADM
BMD ADM
.....


TestPlan Table
MNO
BOL
BMD
DHD
DHFFKFL
XUXN

Basically it works if 1 requirement is linked to the 1 test case or 2 requirements are associated with 1 test case.
However, the main concern is when there is 1 requirement associated with 2 test names.

With this data can u assist please?

 

[PHV]

rk33, ba aware that words like "asap" are not very welcome here.
Where are requirement_id and root_element_id coming from ?
What about some input samples, actual result and expected result ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

The reason you're getting 5 rather than 4 is that you have both fields

Select DISTINCT r.requirement_name as [Functional Specs],
                tt.[Test Name] as [Test Case]

in the sub-query. The thing that is DISTINCT is the combination of the fields and there are 5 such DISTINCT combinations. Just for ease of reference, let's call this

Select r.requirement_name as [Functional Specs],
       tt.[Test Name] as [Test Case]

FROM 
     requirementinfo r LEFT JOIN join 
      (select * from trace t INNER JOIN TestPlan Tp
          on t.trace_element_name = tp.[Test Name]) as tt
     on r.requirement_id = tt.root_element_id)

qryMatches

Then

Select select 'MNOP' as [Workstream],
(Select Count(*) 
 From qryMatches 
 Group By [Functional Specs]) As [Total Functional Specifications],

(Select Count(*) 
 From qryMatches 
 Group By [Test Case]) As [Total Functional Specifications Covered]

FROM (Select TOP 1 * From qryMatches)

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[rk33]

Sorry about that
requirement_id comes from requiremntinfo table
root_element_id comes from Trace table.
Thank you

 

[rk33]

Hi Golom
Thanks but i am using BO to create the report.
I am selecting create your own query option to create this query.
Not sure how i can create a subquery and then the main query?
any ideas?

 

[Golom]

Uhhhh ... what's BO?

In Access, use the query designer; select "New Query"; switch to SQL view (don't select any tables); paste the first bit in and save it with the name "qryMatch".

Then create a second query ... same process but paste in the second blob of code and give it a name that your report references.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

As you didn't post either your actual nor the expected result, I'm just guessing ...
My understanding is that you wanted something like this:
SELECT 'MNOP' AS Workstream, Count(*) AS [Total Functional Specifications]
, (SELECT Count(*) FROM Trace T INNER JOIN TestPlan P ON T.trace_element_name=P.[Test Name]) AS [Total Functional Specifications Covered]
FROM Requirementinfo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rk33]

BO is Business Objects.
The database is Access
I am using BO to report out of Access.
Thanks,

 

[rk33]

The sample data
Requirementinfo Table
Requirement_name
ABC
DEF
ADK
ADM

Trace table
trace element name Requirementname tracedto
MNO DEF
BOL ADM
BMD ADM
.....


TestPlan Table
MNO
BOL
BMD
DHD
DHFFKFL
XUXN

The output detail of this query generated is
Req name Test case
ABC
DEF MNO
ADK
ADM BOL
ADM BMD

Requirement count = 4
and test case count = 3
However because of the usage of distinct what i am getting is a requirement count of 5 and a test case count of 3
The test case count is correct but the requirement count is incorrect
Thanks,

 

[Golom]

If we call the query that generated that last set of output "qryMatch" then

Select  'MNOP' as [Workstream],

(Select Count(*) 
 From (Select [Req Name] From qryMatch 
       Where [Req Name] IS NOT NULL
       Group By [Req Name])) 
As [Total Functional Specifications],

(Select Count(*) 
 From (Select [Test Case] From qryMatch 
       Where [Test Case] IS NOT NULL
       Group By [Test Case])) 
As [Total Functional Specifications Covered]

FROM (Select TOP 1 * From qryMatch) As X

should give you the results you're after. Save that as a query in Access and reference that query as the source of your data in Business Objects.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

So, have you tried my suggestion posted 10 May 06 18:16 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rk33]

Thanks Golom.
appreciate your assistance.
However is there a way to get the complete result in a single query...?
Also
my qryMatches will change as additional criteria has been added.
CODE
Select r.requirement_name as [Functional Specs],
tt.[Test Name] as [Test Case]

FROM
requirementinfo r LEFT JOIN join
(select * from trace t INNER JOIN TestPlan Tp
on t.trace_element_name = tp.[Test Name]) as tt
on r.requirement_id = tt.root_element_id
WHERE r.project_id = 220 and r.hierarchy like '2%'

My entire initial query that i first pasted in this thread returns no rows when run directly in in access.
The same access db is being used for the BO report and when this query is run in BO it does return data.
Any advice will be greatly appreciated.
Thanks,

 

[rk33]

Golom,
Thank you so much the queries worked.
But i am still trying to figure out how i can run both the queries each time the report is refreshed.
Can the queries be written in a .SQL file outside of Access and referenced within BO because in BO at a time i can reference only 1 .SqL file.
Any ideas would be greatly appreciated.

 

[Golom]

If you reference the main query (i.e. the one that internally references qryMatch) in BO then it will run qryMatch internally. There is no need for you to independently run qryMatch or make any mention of it in BO. In fact, that would be pointless since the output of a query is supplied back to its point of invocation which means that it isn't stored anywhere for other routines to use.

You can copy the code from qryMatch into the main query like this

Select  'MNOP' as [Workstream],

(Select Count(*) 
 From (Select [Req Name] From 
          (Select r.requirement_name as [Functional Specs],
                  tt.[Test Name] as [Test Case]

           FROM 
              requirementinfo r LEFT JOIN join 
              (select * from trace t INNER JOIN TestPlan Tp
               on t.trace_element_name = tp.[Test Name]) as tt
           on r.requirement_id = tt.root_element_id)) 
           Where [Req Name] IS NOT NULL
           Group By [Req Name]) ) 
As [Total Functional Specifications],

(Select Count(*) 
 From (Select [Test Case] From 
          ((Select r.requirement_name as [Functional Specs],
                  tt.[Test Name] as [Test Case]

           FROM 
              requirementinfo r LEFT JOIN join 
              (select * from trace t INNER JOIN TestPlan Tp
               on t.trace_element_name = tp.[Test Name]) as tt
           on r.requirement_id = tt.root_element_id)) 
           Where [Req Name] IS NOT NULL
           Group By [Req Name]) 
       Where [Test Case] IS NOT NULL
       Group By [Test Case])) 
As [Total Functional Specifications Covered]

FROM (Select TOP 1 * From 
               ((Select r.requirement_name as [Functional Specs],
                  tt.[Test Name] as [Test Case]

           FROM 
              requirementinfo r LEFT JOIN join 
              (select * from trace t INNER JOIN TestPlan Tp
               on t.trace_element_name = tp.[Test Name]) as tt
           on r.requirement_id = tt.root_element_id)) 
           Where [Req Name] IS NOT NULL
           Group By [Req Name])) As X

but I think you'll agree ... that's really ugly and difficult if not impossible to understand.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[rk33]

Hi Golom,
Appreciate your response.
However due to the database being deleted and recreated daily, i would like to avoid any queries within Access itself.
While i try to run this query i am running into the following errors in terms of syntax.

Since the Access errors are not descriptive enough, i am trying to review the syntax in query analyzer.
Can u please assist?

QUERY ANALYZER ERRORS
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near ')'.
Server: Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'Where'.
Server: Msg 156, Level 15, State 1, Line 42
Incorrect syntax near the keyword 'Where'.

ERROR SEEN on BO while querying Access:
[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '(Select Count(*) : From (Select [Req Name] From : (Select r.requirement_name as [Functional Specs], : tt.[Test Name] as [Test Case] : : FROM : requirementinfo r LEFT OUTER join : (select * fro'.-3100


Any help would be greatly appreciated.
Thanks.

 

[rk33]

Hi Golom,
can you help with this..
Any kind of help will be greated appreciated.
Thanks