Count Occurrences of a value in a new colum

[JungleJme]

SOLVED : Hi, i'm learning Access and am trying to learn the SQL language for queries.

I'm ok building relatively simple queries but am now trying to take the next step by summarizing data with functions. So i am building Queries using the SQL View.

I have a table where our Users log various activities. Each activity has its own row. I can download the whole table and summarise in Excel with Pivots but this is very Resource heavy.

I want to create a query in Access that simply returns a breakdown of User, Customer and how many Activities of a certain type they have had logged against them. I have been trying to use the COUNT and IFF functions in the SELECT part of my statement but keep getting syntax errors.

I have attached a very simplified version of what i am looking for. Any help would be gratefully received!

Thanks

J

 

[pwbrown]

http://stackoverflow.com/questions/...urrences-of-a-column-value-efficiently-in-sql

You should find examples in there that should help you.

 

[pr2-eugin]

You need is a Totals Query.. In other words combination of GROUP BY and COUNT.. Something along the lines of..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Count(tbl_Activity.Activity) AS CountOfActivity
FROM tbl_Activity
WHERE (((tbl_Activity.Activity)="4"))
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

 

[Brianwarnock]

It was probably just a typo but note that it is not IFF but IIF (Immediate IF)

Brian

 

[JungleJme]

Thanks everyone for your quick replies! Wasn't expecting that, am going to investigate these options and get back!

Thanks again,

 

[JungleJme]

You need is a Totals Query.. In other words combination of GROUP BY and COUNT.. Something along the lines of..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Count(tbl_Activity.Activity) AS CountOfActivity
FROM tbl_Activity
WHERE (((tbl_Activity.Activity)="4"))
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

Thanks, i see what we are doing here - filtering out anything that isn't coded as "4" then simply counting up what is left. So i've understood that concept,

What if i wanted to add a layer of complexity - like have two columns of "Count" values. One counts whether the activity is 3 or 4, the other counts if there activities of type 1 or 6. (see attached) I would i go about that? Thanks again for the help

 

[pr2-eugin]

Based on the sample data, the output I got for that is different, You have data for Jane Doe and Paul Nobod twice.. So try the following Query..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Sum(IIF(tbl_Activity.Activity = 1 Or tbl_Activity.Activity = 6,1,0)) AS Activity1Or6, Sum(IIF(tbl_Activity.Activity = 3 Or tbl_Activity.Activity = 4,1,0)) AS Activity4Or3
FROM tbl_Activity
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

I have Summed all the values if they are 4 or 3 in one column and 1 or 6 in the other..

 

[JungleJme]

Based on the sample data, the output I got for that is different, You have data for Jane Doe and Paul Nobod twice.. So try the following Query..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Sum(IIF(tbl_Activity.Activity = 1 Or tbl_Activity.Activity = 6,1,0)) AS Activity1Or6, Sum(IIF(tbl_Activity.Activity = 3 Or tbl_Activity.Activity = 4,1,0)) AS Activity4Or3
FROM tbl_Activity
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

I have Summed all the values if they are 4 or 3 in one column and 1 or 6 in the other..

Thanks very much i will try this now, literally just as this e-mail came through i was trying

sum(case when tbl_Activity.Activity IN (3,4) then 1 else null end) Visits,
sum(case when tbl_Activity.Activity IN (1,6) then 1 else null end) Calls

It has returned "something" - but not yet checked if it is right!

Now i can try both of them and see what the output is, thanks very much for the help!

 

[JungleJme]

I couldn't get your IIF statement to work, Access kept returning an "Incorrect Syntax Near '=' " error...

My SUM/CASE kind of worked but returns two rows for each customer

i.e

Customer1: 3 ""
Customer1: "" 12

Instead of Customer1: 3 12

...getting closer though!!

 

[pr2-eugin]

What is the type of Activity? If it is text, try the following..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Sum(IIF(tbl_Activity.Activity = "1" Or tbl_Activity.Activity = "6",1,0)) AS Activity1Or6, Sum(IIF(tbl_Activity.Activity = "3" Or tbl_Activity.Activity = "4",1,0)) AS Activity4Or3
FROM tbl_Activity
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

The code I gave earlier is for Type - Number..

 

[JungleJme]

I had this problem at the very start of trying to figure this out. I tried the IIF and got that same syntax error - have tried "1" & '1' - i've checked the field on the table and the type is AUTONUMBER - is that an issue?

 

[pr2-eugin]

That sounds strange.. I just created a quick table and checked.. It works fine..

SELECT tbl_Activity.Person, tbl_Activity.Customer, Sum(IIF(tbl_Activity.Activity IN (1,6),1,0)) AS Activity1Or6, Sum(IIF(tbl_Activity.Activity IN (3,4),1,0)) AS Activity4Or3
FROM tbl_Activity
GROUP BY tbl_Activity.Person, tbl_Activity.Customer;

I do not think Auto number would cause a problem..

 

[JungleJme]

Wahoo! I got there, the duplicate lines were caused by the fact that i had included the "Activity" field in the Group By Clause.

Thanks for your help!

SELECT
tbl_Activity.Person,
tbl_Activity.Customer,

sum(case when a.type_fk IN (3,4) then 1 else 0 end) Visits,
sum(case when a.type_fk IN (1,6) then 1 else 0 end) Calls

FROM tbl_Activity
GROUP BY tbl_Activity.Person, tbl_Activity.Customer
Order BYtbl_Activity.Person