New to Forum - Need some help pls (1 Viewer)

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

You can do the following in the SQL view of a new query

SELECT [Data-Billing].[Employee Number], [Employee Cost centre], Count([Data-Billing].[Employee Number]) as NumberofCCs
FROM [Data-Billing]
GROUP BY [Data-Billing].[Employee Number], [Employee Cost centre]

If you want to do this using the design grid view of the query, add the data-billing table and the two fields. Then add the employee number field a second time. Right click on this column in the grid and select Totals. When you select Totals, a new row shows up in the grid called Totals. In the totals row, change Group By to Count for this third column. The first two fields should stay as Group By

Skinny

I am not sure if this will help but in Design Quert you can add an expression for a new column. I have used it to do totals and percentages for example. I am not SQL savy either so the builder really helps. Other might have a better solution, would like to hear also.

Daz

jzwp22 - thanks for getting involved however we've lost the distinct factor in your new code. For e.g.

SELECT Count(*) AS NumberofCCs
FROM (SELECT DISTINCT [Employee Cost centre] FROM [Data-Billing]) AS [Unique CCs];

This works and it gives me 88 distinct cost centres. Your code doesn't have distinct anywhere in it and when you introduce the new element i.e. Employee Number, the cost centre count becomes number of total values as opposed to no. of distinct values.

I understand access SQL doesn't support count distinct so from what I read I need some kind of subquery which is a bridge too far for my weak SQL.

Any takers?

Sorry that I misunderstood.

You could use a query to get the distinct combinations of employee and cost center and then create a second query based on that first one to do the counting

query name: qryTheDistinctValues

SELECT DISTINCT [Data-Billing].[Employee Number], [Data-Billing].[Employee Cost centre]
FROM [Data-Billing];


Now the counting query:

SELECT qryTheDistinctValues.[Employee Number], Count(qryTheDistinctValues.[Employee Cost centre]) AS [CountOfEmployee Cost centre]
FROM qryTheDistinctValues
GROUP BY qryTheDistinctValues.[Employee Number];


If you want to use 1 nested query as you showed in your post, it would look like this

SELECT qryTheDistinctValues.[Employee Number], Count(qryTheDistinctValues.[Employee Cost centre]) AS [CountOfEmployee Cost centre]
FROM (SELECT DISTINCT [Data-Billing].[Employee Number], [Data-Billing].[Employee Cost centre]
FROM [Data-Billing]) qryTheDistinctValues
GROUP BY qryTheDistinctValues.[Employee Number];

Thanks for all your help

You're welcome. Just to make sure, did the query scheme I proposed give you what you were after?

As it happens I managed to get the below to do what I wanted (after more net scouring and SQL nudging) but having tested yours just now I can confirm it also does indeed work. Diff code but same results.

SELECT [Data-Billing].[Employee Number] AS [Emp No], Count([Data-Billing].[Employee Cost centre]) AS CountOfCC
FROM (SELECT [Data-Billing].[Employee Number], [Data-Billing].[Employee Cost centre] FROM [Data-Billing] GROUP BY [Data-Billing].[Employee Number], [Data-Billing].[Employee Cost centre]) AS Step1
GROUP BY [Data-Billing].[Employee Number];

Cheers again.

Glad to hear that you got to a solution.