Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 02-04-2013, 10:22 AM   #1
skinnyJ
Newly Registered User
 
Join Date: Feb 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
skinnyJ is on a distinguished road
New to Forum - Need some help pls

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

skinnyJ is offline   Reply With Quote
Old 02-04-2013, 11:15 AM   #2
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: New to Forum - Need some help pls

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

Last edited by jzwp22; 02-04-2013 at 11:36 AM.
jzwp22 is offline   Reply With Quote
Old 02-04-2013, 11:21 AM   #3
Daz
Newly Registered User
 
Join Date: Jan 2013
Posts: 28
Thanks: 4
Thanked 0 Times in 0 Posts
Daz is on a distinguished road
Re: New to Forum - Need some help pls

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

Daz is offline   Reply With Quote
Old 02-05-2013, 01:44 AM   #4
skinnyJ
Newly Registered User
 
Join Date: Feb 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
skinnyJ is on a distinguished road
Re: New to Forum - Need some help pls

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?
skinnyJ is offline   Reply With Quote
Old 02-05-2013, 04:45 AM   #5
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: New to Forum - Need some help pls

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];
jzwp22 is offline   Reply With Quote
Old 02-05-2013, 05:25 AM   #6
skinnyJ
Newly Registered User
 
Join Date: Feb 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
skinnyJ is on a distinguished road
Re: New to Forum - Need some help pls

Thanks for all your help
skinnyJ is offline   Reply With Quote
Old 02-05-2013, 05:36 AM   #7
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: New to Forum - Need some help pls

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

jzwp22 is offline   Reply With Quote
Old 02-05-2013, 05:45 AM   #8
skinnyJ
Newly Registered User
 
Join Date: Feb 2013
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
skinnyJ is on a distinguished road
Re: New to Forum - Need some help pls

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.
skinnyJ is offline   Reply With Quote
Old 02-05-2013, 07:22 AM   #9
jzwp22
Access Hobbyist
 
Join Date: Mar 2008
Posts: 2,629
Thanks: 0
Thanked 311 Times in 308 Posts
jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about jzwp22 has a spectacular aura about
Re: New to Forum - Need some help pls

Glad to hear that you got to a solution.

jzwp22 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
New to the forum Chris CTP Introduce Yourself 1 10-15-2012 10:34 AM
New with this ID, but not new to the forum DBill Introduce Yourself 0 07-09-2012 04:29 PM
New to the forum puzzles7 Introduce Yourself 1 04-02-2012 08:38 AM
New To The Forum tabbycat1234 Introduce Yourself 0 07-17-2011 05:17 AM
New to this forum hotski General 0 04-01-2006 05:28 AM




All times are GMT -8. The time now is 02:20 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World