help with the query

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?
Roman FAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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.
Amitkumar PSr. ConsultantCommented:
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 ConsultantCommented:
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

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Éric MoreauSenior .Net ConsultantCommented:
forget it, didn't see you are using Access!
Roman FAuthor 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 ConsultantCommented:
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

PatHartmanCommented:
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 ConsultantCommented:
I would try with this simple query 1 (no group by at all):
Select DISTINCT  Project, Type From YourTable

Open in new window

Roman FAuthor 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
Roman FAuthor Commented:
guys, please check
my combined query is not working
Éric MoreauSenior .Net ConsultantCommented:
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

Rey Obrero (Capricorn1)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
PatHartmanCommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
thank you
Rey Obrero (Capricorn1)Commented:
???
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.