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

[Pebsanne]

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.

[Killer42]

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?

[MMcCarthy]

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;

[NeoPa]

You could look at CrossTab queries.
Very inexperienced in those myself, but this is worth a look I think.

[MMcCarthy]

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

[NeoPa]

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.

SELECT 1 As TempVal, 
Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
FROM people
UNION
SELECT 2 AS TempVal,
Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
FROM people
UNION
SELECT 3 AS TempVal, 
Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
FROM people
UNION
SELECT 4 AS TempVal,
Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
FROM people
UNION
SELECT 5 AS TempVal, 
Sum(IIf([Workshop1]=[TempVal],[TempVal],0)) AS CountWk1,
Sum(IIf([Workshop2]=[TempVal],[TempVal],0)) AS CountWk2,
Sum(IIf([Workshop3]=[TempVal],[TempVal],0)) AS CountWk3,
Sum(IIf([Workshop4]=[TempVal],[TempVal],0)) AS CountWk4
FROM people;

PS. Yes Mary, I see now why you chose this approach.

[MMcCarthy]

Hi Adrian

Good expansion on my solution. Should work well.

Mary

[Killer42]

[quote=NeoPa]

SELECT 1 As TempVal, 
.
.
.
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.

[MMcCarthy]

[quote=Killer42]

SELECT 1 As TempVal, 
.
.
.
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:

 
SELECT 1 As TempVal, 
Count(DLookup("[Workshop1]","people","[Workshop1]=" & [TempVal]) AS CountWk1,
Count(DLookup("[Workshop2]","people","[Workshop2]=" & [TempVal]) AS CountWk2,
Count(DLookup("[Workshop3]","people","[Workshop3]=" & [TempVal]) AS CountWk3,
Count(DLookup("[Workshop4]","people","[Workshop4]=" & [TempVal]) AS CountWk4
UNION
 
etc........
 
 

[Killer42]

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.

[MMcCarthy]

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