Aggregation Query doubt

[akshayajmani]

I Have a table as follows:

POOLID TIME
101 1
101 2
101 1
102 1
102 2

This table gets populated with data from some other source which is not in my control.

This table should have unique combination of (PoolID,Time) i.e a poolid, time combination should repeat only once in the data. I do not want to implement this in table design logic but want a query that gives me as output all pool ids for which poolid,time combination is not unqiue.


So the output should be 101 in this case (PoolID -101 , Time- 1 combination is not unique i.e it is repeating twice in the data)

I have the following query which does this but it works fine in oracle and not in MS ACCESS. Kindly help

select table3.poolid as poolid from
(select poolid as poolid,count(*) as frequency1 from temp group by poolid order by poolid asc) table3
,(select poolid as poolid ,count(*) as frequency2 from


(select distinct poolid as poolid, time as time1 from temp order by poolid asc) table1 group by poolid order by poolid asc) table2
where
table3.poolid = table2.poolid
and
table3.frequency1!=table2.frequency2

[June7]

The aliases are causing circular reference error and why is there ! character?

This returns one record for 102:

Select table3.poolid from
(select poolid, count(*) as frequency1 from temp group by poolid order by poolid asc) as table3
,(select poolid, count(*) as frequency2 from
(select distinct poolid, time as time1 from temp order by poolid asc) table1 group by poolid order by poolid asc) as table2
where
table3.poolid = table2.poolid
and
table3.frequency1=table2.frequency2;

[akshayajmani]

Thanks for the response. But your query is same as mine. How come it is working for you. ! character is because i want the solution to be 101 and not 102. Please let me know which query is working

[recyan]

Not related to the question, as to why the code is not working ( all the same, just chk out ) :

SELECT 
	tblPOOLS.POOLID, 
	tblPOOLS.TIME, 
	Count(tblPOOLS.TIME) AS CountOfTIME
FROM 
	tblPOOLS
GROUP BY 
	tblPOOLS.POOLID, 
	tblPOOLS.TIME
HAVING 
	(((Count(tblPOOLS.TIME))>1));

Thanks

[June7]

My query is not exactly the same. I removed the alias fieldnames. The ! does not work in Access query. Never seen it before. How does it make the result 101 not 102?

Recyan's suggestion looks promising.