Using SELECT DISTINCT in nested/subqueries

[VictoriaJones]

I wonder if anyone knows if it is possible to use the SELECT DISTINCT clause in a nested/sub query. I currently run the following query as two stand-alone append queries (1 to SELECT DISTINCT the records and the 2nd one to COUNT the records), but would like to run it as 1 nested query. I have written the following SQL:

INSERT INTO tbl_Count_FunctionIDDept ( [Count of Function ID], [IT Department] )
SELECT Count(tbl_DataAnalysis1.[Function ID]) AS [CountOfFunction ID], tbl_DataAnalysis1.[IT Department]
FROM tbl_DataAnalysis1
WHERE EXISTS(SELECT DISTINCT tbl_DataAnalysis1.[Function ID], tbl_DataAnalysis1.[IT Department] FROM tbl_DataAnalysis1)
GROUP BY tbl_DataAnalysis1.[IT Department];

This runs but does not SELECT DISTINCT the records, just gives me all the duplicates as well. IS SELECT DISTINCT possible, or am I simply doing something very stupid

Any possible answers gratefully received!!!!

 

[mdthornton]

As it stands your subquery isn't really doing anything for you. When you use exists like this there is normally a join from the outer select to a table in the subquery. As it stands the subquery always returns something and therefore the exists clause is always true.

In this case I would do the following:

SELECT DISTINCT tbl_DataAnalysis1.[Function ID], tbl_DataAnalysis1.[IT Department]
FROM tbl_DataAnalysis1

save this as queryX. Then do your count/insert:

INSERT INTO tbl_Count_FunctionIDDept ( [Count of Function ID], [IT Department] )
SELECT Count([queryX].[Function ID]) AS [CountOfFunction ID], [queryX].[IT Department]
FROM queryX
GROUP BY [queryX].[IT Department];

Best Regards,
Mike

 

[VictoriaJones]

Mike,

Brilliant - works like a charm!!

Thanks for your help
Victoria