469,602 Members | 1,699 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,602 developers. It's quick & easy.

how do I chart the number of distinct occurances of several workshops?

Hi,
How do I chart or calculate a report to count the distinct occurances of the values 1 to 5 for lets say 10 workshops. I have the following table in MS Access:
ID Workshop1 Workshop2 Workshop3 Workshop4 ....
1 ..... 3 ............... 2 ..................4 .................1
2 ......2 .................2 ..................2.................5
3..... 2 .................1 ...................5.................5
4 ..... 3..................3.................. 4 ............... 3
......etc

I counted the number of occurances for one workshop:
SELECT DISTINCT people.workshop1, Count(people.workshop1) AS CountOfworkshop1 FROM people GROUP BY people.workshop1;
But I need to chart it for about 10 workshops in the one graph/chart in MS Access.
thanks.
Nov 6 '06 #1
10 1366
Killer42
8,435 Expert 8TB
Hi,
How do I chart or calculate a report to count the distinct occurances of the values 1 to 5 for lets say 10 workshops. I have the following table in MS Access:
ID Workshop1 Workshop2 Workshop3 Workshop4 ....
1 ..... 3 ............... 2 ..................4 .................1
2 ......2 .................2 ..................2.................5
3..... 2 .................1 ...................5.................5
4 ..... 3..................3.................. 4 ............... 3
......etc

I counted the number of occurances for one workshop:
SELECT DISTINCT people.workshop1, Count(people.workshop1) AS CountOfworkshop1 FROM people GROUP BY people.workshop1;
But I need to chart it for about 10 workshops in the one graph/chart in MS Access.
thanks.
Just a question for clarification - are these 10 "workshops" 10 fields (or groups of fields) in a table? Or 10 separate tables? Or 10 records in a table? From the layout shown, I'm guessing the first option?
Nov 7 '06 #2
MMcCarthy
14,534 Expert Mod 8TB
Hi,
How do I chart or calculate a report to count the distinct occurances of the values 1 to 5 for lets say 10 workshops. I have the following table in MS Access:
ID Workshop1 Workshop2 Workshop3 Workshop4 ....
1 ..... 3 ............... 2 ..................4 .................1
2 ......2 .................2 ..................2.................5
3..... 2 .................1 ...................5.................5
4 ..... 3..................3.................. 4 ............... 3
......etc

I counted the number of occurances for one workshop:
SELECT DISTINCT people.workshop1, Count(people.workshop1) AS CountOfworkshop1 FROM people GROUP BY people.workshop1;
But I need to chart it for about 10 workshops in the one graph/chart in MS Access.
thanks.
Unfortunately, there is no easy way to do this. The following will cover 4 workshop fields you will need to adapt it to cover 10.

SELECT "1" As TempVal,
Sum(IIf([workshop1]=1,1,0)) AS CountWk1,
Sum(IIf([workshop2]=1,1,0)) AS CountWk2,
Sum(IIf([workshop3]=1,1,0)) AS CountWk3,
Sum(IIf([workshop4]=1,1,0)) AS CountWk4,
FROM people
UNION
SELECT "2" AS TempVal,
Sum(IIf([workshop1]=2,1,0)) AS CountWk1,
Sum(IIf([workshop2]=2,1,0)) AS CountWk2,
Sum(IIf([workshop3]=2,1,0)) AS CountWk3,
Sum(IIf([workshop4]=2,1,0)) AS CountWk4
FROM people
UNION
SELECT "3" AS TempVal,
Sum(IIf([workshop1]=3,1,0)) AS CountWk1,
Sum(IIf([workshop2]=3,1,0)) AS CountWk2,
Sum(IIf([workshop3]=3,1,0)) AS CountWk3,
Sum(IIf([workshop4]=3,1,0)) AS CountWk4
FROM people
UNION
SELECT "4" AS TempVal,
Sum(IIf([workshop1]=4,1,0)) AS CountWk1,
Sum(IIf([workshop2]=4,1,0)) AS CountWk2,
Sum(IIf([workshop3]=4,1,0)) AS CountWk3,
Sum(IIf([workshop4]=4,1,0)) AS CountWk4
FROM people
UNION
SELECT "5" AS TempVal,
Sum(IIf([workshop1]=5,1,0)) AS CountWk1,
Sum(IIf([workshop2]=5,1,0)) AS CountWk2,
Sum(IIf([workshop3]=5,1,0)) AS CountWk3,
Sum(IIf([workshop4]=5,1,0)) AS CountWk4
FROM people;
Nov 7 '06 #3
NeoPa
32,200 Expert Mod 16PB
You could look at CrossTab queries.
Very inexperienced in those myself, but this is worth a look I think.
Nov 7 '06 #4
MMcCarthy
14,534 Expert Mod 8TB
You could look at CrossTab queries.
Very inexperienced in those myself, but this is worth a look I think.
I tried it first as a crosstab query but because of the table structure I don't think it will work.

But as you say worth another look if anyone has any suggestions.

Mary
Nov 7 '06 #5
NeoPa
32,200 Expert Mod 16PB
I'm not positive this will work, but if it does, it should make it a little easier to amend.
I tried creating a UNION query with just the TempVals first, but it insisted on a FROM clause.
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 As TempVal, 
  2. Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
  3. Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
  4. Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
  5. Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
  6. FROM people
  7. UNION
  8. SELECT 2 AS TempVal,
  9. Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
  10. Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
  11. Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
  12. Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
  13. FROM people
  14. UNION
  15. SELECT 3 AS TempVal, 
  16. Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
  17. Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
  18. Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
  19. Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
  20. FROM people
  21. UNION
  22. SELECT 4 AS TempVal,
  23. Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
  24. Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
  25. Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
  26. Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
  27. FROM people
  28. UNION
  29. SELECT 5 AS TempVal, 
  30. Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
  31. Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
  32. Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
  33. Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
  34. FROM people;
PS. Yes Mary, I see now why you chose this approach.
Nov 8 '06 #6
MMcCarthy
14,534 Expert Mod 8TB
Hi Adrian

Good expansion on my solution. Should work well.

Mary
Nov 8 '06 #7
Killer42
8,435 Expert 8TB
[quote=NeoPa]
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 As TempVal, 
  2. .
  3. .
  4. .
  5. FROM people;
No offence intended NeoPa, but SQL syntax sure forces some silly-looking and long-winded solutions on us at times... :)
On the plus side, I suppose that shows how flexible it is.
Nov 8 '06 #8
MMcCarthy
14,534 Expert Mod 8TB
[quote=Killer42]
Expand|Select|Wrap|Line Numbers
  1. SELECT 1 As TempVal, 
  2. .
  3. .
  4. .
  5. FROM people;
No offence intended NeoPa, but SQL syntax sure forces some silly-looking and long-winded solutions on us at times... :)
On the plus side, I suppose that shows how flexible it is.
It requires the FROM people because it is referencing the Workshop1, Workshop2, etc.

You could write it as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT 1 As TempVal, 
  3. Count(DLookup("[Workshop1]","people","[Workshop1]=" & [TempVal]) AS CountWk1,
  4. Count(DLookup("[Workshop2]","people","[Workshop2]=" & [TempVal]) AS CountWk2,
  5. Count(DLookup("[Workshop3]","people","[Workshop3]=" & [TempVal]) AS CountWk3,
  6. Count(DLookup("[Workshop4]","people","[Workshop4]=" & [TempVal]) AS CountWk4
  7. UNION
  8.  
  9. etc........
  10.  
  11.  
Nov 8 '06 #9
Killer42
8,435 Expert 8TB
It requires the FROM people because it is referencing the Workshop1, Workshop2, etc. ...
Oh, I wasn't referring to the FROM specifically. It was just a comment about SQL in general. Like having to repeat big long functions in the selection criteria and the fields to be returned, that sort of thing.
Nov 8 '06 #10
MMcCarthy
14,534 Expert Mod 8TB
Oh, I wasn't referring to the FROM specifically. It was just a comment about SQL in general. Like having to repeat big long functions in the selection criteria and the fields to be returned, that sort of thing.
Totally agree with you. Would be great if we could pass multiple parameters to functions and get multiple returns. I suppose in theory if you redesigned the function with a variant array as a parameter and a variant array as a return you could get multiple values but SQL won't then allow you to create multiple Aliases for the field values.

Mary
Nov 8 '06 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

22 posts views Thread by PeteCresswell | last post: by
1 post views Thread by rkbnair | last post: by
14 posts views Thread by Haines Brown | last post: by
7 posts views Thread by John | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.