display all divisions,even if not present in criteria

[gogaak]

hello all,
I have two tables related to employee and his ratings....



Table: Employee
-----------------------
TokenID (pk)
Division
Post
Table: PmRating
-----------------------
SerialNO
TokenID (FK)
H1

In this organization there are 5 Posts
Some division may have all 5 , some may have less..
EL
M
O
PUL
PGL

When i give a sql query:

SELECT Avg(PMRating.H1) AS AvgOfH1, Employee.Post, Count(Employee.TokenID) AS CountOfTokenID
FROM Employee LEFT JOIN PMRating ON Employee.TokenID = PMRating.TokenID
WHERE (((Employee.Division)='PDRDS'))
GROUP BY Employee.Post;
This is wat I get in return of the sql query

Average_H1 Post Emp_Count
----------------------------------------
4.3 M 12
0.0 O 14
3.4 PUL 10
4.5 PGl 10

What I need is that it should display all bands even if in that division there is no employee for a particular 'Post' ( in the above example 'EL' is missing in the 'Post' field because there is no employee at the EL post for this PDRDS division).
In short I would like to see the average ratings of a division grouped by the Post levels, but for all posts in the organization, even if there is no employee at that post.
Something like this.
Average_H1 Post Emp_Count
------------------------------------------
0.0 EL 0
4.3 M 12
0.0 O 14
3.4 PUL 10
4.5 PGl 10


I have also attached a sample of the data stored in the two tables
i have pasted it in Excel and attached as ZIP....

please do check...I'm in desperate need of help..

 

[neileg]

Create a query based on the Employee table that returns only Post and make it a unique values query. This will return your five post types. Use this query as the starting point for your query and make it left join your existing query.

 

[gogaak]

Create a query based on the Employee table that returns only Post and make it a unique values query. This will return your five post types. Use this query as the starting point for your query and make it left join your existing query.

yes I tried that.....here it is this shows me all bands/posts...

SELECT DISTINCT Employee.Band,Avg(PMRating.H1) AS AvgOfH1, Count(Employee.TokenID) AS CountOfTokenID
FROM Employee LEFT JOIN PMRating ON Employee.TokenID = PMRating.TokenID
GROUP BY Employee.Band;

this returns me all bands/posts
Average_H1 Post Emp_Count
------------------------------------------
0.0 EL 7
4.3 M 12
0.0 O 14
3.4 PUL 10
4.5 PGl 10


But the moment I put a criteria for division

SELECT DISTINCT Employee.Band,Avg(PMRating.H1) AS AvgOfH1, Count(Employee.TokenID) AS CountOfTokenID
FROM Employee LEFT JOIN PMRating ON Employee.TokenID = PMRating.TokenID
WHERE Employee.Division='PDRDS'
GROUP BY Employee.Band;

I get this in return

Average_H1 Post Emp_Count
----------------------------------------
4.3 M 12
0.0 O 14
3.4 PUL 10
4.5 PGl 10

Where 'EL' band is not included
i hope i made my point clear...please bear with me ..thanx

 

[gogaak]

hey guys....
I am still stuck with this problem....

any solution remotely close to the problem is also welcome...
I reallly need this to work...
please help...

 

[neileg]

There are fields in your queries that don tally with the structure you set out in the posting. Could you post a stripped down version of your database to see if I can make sense of it.

Your SQL is not what I had in mind.

 

[gogaak]

There are fields in your queries that don tally with the structure you set out in the posting. Could you post a stripped down version of your database to see if I can make sense of it.

Your SQL is not what I had in mind.

Thanx buddy....i have found a way to do it...
Using crosstab query...Thats much more easier...

Your suggestion was great...but i guess for a novice like me it will take some time to understand....Anyways for now i have the solution, but i would definetley try to device some method..the way you suggested...

Thanx again, take care

 

[neileg]

Great! Bear in mind that the crosstab approach will be fine so long as the post appears somewhere. If you, for instance, created a new post, this wouldn't appear in the results if no one held the post. Don't know if this is important.

 

[gogaak]

Great! Bear in mind that the crosstab approach will be fine so long as the post appears somewhere. If you, for instance, created a new post, this wouldn't appear in the results if no one held the post. Don't know if this is important.

exactly...i got that...
But the crosstab query gives me much more scope of producing results based on different conditions..

thats why i said...its not giving me completely what i want....but atleast i learnt some new method....

thank you..

 

[neileg]

Here's a quick example of what I mean.