I'm sure this is so simple for most access heads but I haven't really graduated from GUI to SQL yet and scouring the net hasn't helped (so no links to clever articles clearly above my head as I've probably seen them already). I need code to count distinct values per another field from the same table.
I want this:
SELECT Count(*) AS NumberofCCs
FROM (SELECT DISTINCT [Employee Cost centre] FROM [Data-Billing]) AS [Unique CCs];
to be broken down by another dimension within the table i.e. this:
[Data-Billing].[Employee Number]
So the end result is 2 columns: Employee Number / Distinct cost centre count, Eg:
ABCD123 1
ABCD124 1
ABCD125 3
ABCD126 2
If anyone can show me the code or tell me how to do it via the GUI I'd be grateful.
Thanks
I want this:
SELECT Count(*) AS NumberofCCs
FROM (SELECT DISTINCT [Employee Cost centre] FROM [Data-Billing]) AS [Unique CCs];
to be broken down by another dimension within the table i.e. this:
[Data-Billing].[Employee Number]
So the end result is 2 columns: Employee Number / Distinct cost centre count, Eg:
ABCD123 1
ABCD124 1
ABCD125 3
ABCD126 2
If anyone can show me the code or tell me how to do it via the GUI I'd be grateful.
Thanks