help with the query

Roman F
Roman F used Ask the Experts™
on
i need your help
I have a table with many fields, just look at two fields:Project and Type
Project       Type
662047      Type 2
662047      Type 2
662047      Type 2
538739      Type 1
538739   Type 1
538739      Type 1
556238      Type 1
575063      Type 1
575069      Type 1
575069      Type 2
575176      Type 1
575176      Type 1
575190      Type 1
575190      Type 1
575247      Type 1
575247      Type 1
575328      Type 1
575328      Type 2



as you can see many of the Projects have the same Type, like 662047 has two Type 2, some of the projects have more then one Type1, like 538739 has three Type1, some of the projects like 575069 has Type 1 and Type 2.

how to get just those with two differenct Types?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Distinguished Expert 2017
Commented:
This is a two step process for Access SQL.  You can create a query with a subquery or you can create two separate queries.  I'll do method 2 since it is simpler and requires less knowledge of SQL Server.

query1
Select Project, Type From YourTable
Group By Project, Type;

query2
Select Project From query1
Group By Project
Having Count(*) > 1;

Query 1 groups the data so you have a list of distinct project/type pairs.  query 2 lists the projects that exist more than once in query 1.
Commented:
Do a grouping by Project and check for count of type > 1.

Below is the query from Oracle.

select
    Project,
    Type
from
    ProjectType
where
    Project in (
        select
             Project
        from
            ProjectType
        group by
            Project
        having
             Count(Type) > 1)
Éric MoreauSenior .Net Consultant
Top Expert 2016
Commented:
This is my test:

DECLARE @test TABLE (Project INT, [Type] VARCHAR(10))

INSERT INTO @test ( Project, Type )
VALUES  ( 662047, 'Type 2')
, ( 662047, 'Type 2')
, ( 662047, 'Type 2' )
, ( 538739, 'Type 1' )
, ( 538739, 'TYPE 1' )
, ( 538739, 'Type 1' )
, ( 556238, 'Type 1' )
, ( 575063, 'Type 1' )
, ( 575069, 'Type 1' )
, ( 575069, 'Type 2' )
, ( 575176, 'Type 1' )
, ( 575176, 'Type 1' )
, ( 575190, 'Type 1' )
, ( 575190, 'Type 1' )
, ( 575247, 'Type 1' )
, ( 575247, 'Type 1' )
, ( 575328, 'Type 1' )
, ( 575328, 'Type 2' )


SELECT * FROM @test

SELECT A.Project
FROM (
	SELECT DISTINCT * FROM @test
) AS A
GROUP BY A.Project
HAVING	COUNT(*) > 1

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
forget it, didn't see you are using Access!

Author

Commented:
the only projects should i get are : 575069 and 575328 because they have Type 1 and Type 2
your query does not work, sorry
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
If you add DISTINCT to the query of PatHartman, it should work!

Select DISTINCT  Project, Type From YourTable
 Group By Project, Type;

Open in new window

Distinguished Expert 2017

Commented:
Eric, Group by, in this case is the same as Distinct so my first query is fine.  

The ONLY reason for the separate first query is to get the distinct list since Access cannot do this "group by" and count in a single step.  

To use the subqueries as Eric shows, I think you need two nestings.  The innermost nesting to get the Distinct (or Group By) and the outer subquery to count the instances of Project.  That's the reason I went with two queries.  It is very simple plus you can build it with QBE if you are unfamiliar with SQL syntax whereas the subquery method needs to be built in SQL view since it is too hard to see the subquery in QBE view.
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
I would try with this simple query 1 (no group by at all):
Select DISTINCT  Project, Type From YourTable

Open in new window

Author

Commented:
to PatHartman: i am sorry, the queries are fine, working :). Thank you
now, how to combine two in one?

Select Project, Type From ProjectDetail
where Project in
(
Select Project From ProjectDetail
Group By Project
Having Count(*) > 1
)
Group By Project, Type;


:( not working

Author

Commented:
guys, please check
my combined query is not working
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
have you tried:

SELECT A.Project
FROM (
	SELECT DISTINCT Project, Type  FROM ProjectDetail
) AS A
GROUP BY A.Project
HAVING	COUNT(*) > 1

Open in new window

Top Expert 2016

Commented:
try this query

SELECT T.Project, Count(T.Project) AS CountOfProject
FROM
(SELECT Project,Type
FROM ProjectDetail
GROUP BY Project, Type
) As T
GROUP BY T.Project
HAVING Count(T.Project)>1
Distinguished Expert 2017
Commented:
to PatHartman: i am sorry, the queries are fine, working :). Thank you
now, how to combine two in one?
I explained to you why I did this as two separate queries.  You have to force Access to make a distinct list of Project and Type before you can find the projects with multiple types.  Rey's solution should work.

Author

Commented:
thank you
Top Expert 2016

Commented:
???

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial