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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Éric Moreau
forget it, didn't see you are using Access!
ASKER
the only projects should i get are : 575069 and 575328 because they have Type 1 and Type 2
your query does not work, sorry
your query does not work, sorry
If you add DISTINCT to the query of PatHartman, it should work!
Select DISTINCT Project, Type From YourTable
Group By Project, Type;
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.
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.
I would try with this simple query 1 (no group by at all):
Select DISTINCT Project, Type From YourTableASKER
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
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
ASKER
guys, please check
my combined query is not working
my combined query is not working
have you tried:
SELECT A.Project
FROM (
SELECT DISTINCT Project, Type FROM ProjectDetail
) AS A
GROUP BY A.Project
HAVING COUNT(*) > 1
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you