Query Restriction

[gogaak]

hello all,
I am using MS Access 97.
I am trying to work out a query to extract the employee ratings based on certain matches...

i have attached a text explaining the structure and the type of results I am trying to get...

Please...If you could take time out to check this....it wud be great
Any reply wud be great..

Thanks a lot..

 

[Jon K]

Create a table tblPost with one field Post. Put the posts in five records. In a new query, link this table to your query with a left join and use Nz() to display the 0 for EL.

SELECT [Your Query].[AvgOfH1], [tblPost].[Post], Nz([Your Query].[CountOfTokenID])+0 AS Emp_Count
FROM tblPost LEFT JOIN [Your Query] ON [tblPost].[Post]=[Your Query].[Post];
.

 

[gogaak]

Create a table tblPost with one field Post. Put the posts in five records. In a new query, link this table to your query with a left join and use Nz() to display the 0 for EL.

SELECT [Your Query].[AvgOfH1], [tblPost].[Post], Nz([Your Query].[CountOfTokenID])+0 AS Emp_Count
FROM tblPost LEFT JOIN [Your Query] ON [tblPost].[Post]=[Your Query].[Post];
.

Thanx a lot..
Well i cant afford to add another table...coz that would need updating in the future...so evry time there is an updation in the number or type of posts then i'll hav to create provision for that....its not tough,,,but this increases my reqts for the project...

By the way what does Nz() do..............
where can i have alook at such functions which can be used in Access queries..?

 

[Jon K]

Would extracting a unique post list from the Employee table produce a list of just the five posts? i.e.

SELECT distinct Employee.Post
FROM Employee;

If it does, you can use this query instead of the post table. Somehow you need to provide the list for the Outer Join.


Nz() is one of Access's built-in functions. It converts Null values to zeros when involved in calculations.
It can also be used on a text field to display whatever you want it to display when the field is null e.g. Nz([FieldName], "DisplayThis")
.

 

[gogaak]

Would extracting a unique post list from the Employee table produce a list of just the five posts? i.e.

SELECT distinct Employee.Post
FROM Employee;

If it does, you can use this query instead of the post table. Somehow you need to provide the list for the Outer Join.


Nz() is one of Access's built-in functions. It converts Null values to zeros when involved in calculations.
It can also be used on a text field to display whatever you want it to display when the field is null e.g. Nz([FieldName], "DisplayThis")
.

Thanx man

Extracting all the posts in the organization is possible ...but how would i be able to link it to the average rating that I am calculating for evry post in that particular division.....
The problem here is that evry division dusnt necessarily have all 5 posts...
so the number of records returned may vary with evry division...
And this becomes difficult to handel when i am trying to put all these in tables using ASP..

please follow this up...
thank u

 

[gogaak]

oh ..please come on....please sumbody help....

Any input would do....i could think of sum alternative from that....

Thanx a lot