Hi all,

Firstly I would like to say hello to everyone as this is my first post!

I have been on an Access to ASP project for a few months now, and when I thought it was all finished, I have come across all sorts of problems! I would greatly appreciate a few minutes of anyones time who are proficient in queries.

I have the following query in Access which is OK! Works brilliantly and this is:


SELECT [LastName] & ", " & [FirstName] AS Employee, Salary, Commission, ([Salary]+Nz([Commission],0)) AS [Total Salary], [BranchID] AS Branch, DeptID AS Department, Position
FROM tblEmployees AS e1
WHERE ([Salary]+[Commission])>=(SELECT AVG ([Salary]+[Commission])
FROM tblEmployees e2
WHERE ((e2.BranchID=e1.BranchID And
(e2.BranchID=Forms!AvgDialog!Branch) Or
(e2.BranchID=Forms!AvgDialog![Branch] Is Null) Or ((e2.BranchID) Like
IIf(forms!AvgDialog!Text10="ALL","*",forms!AvgDialog!Text10))) And

(e2.Position=e1.Position And (e2.Position=Forms!AvgDialog!Position) Or
(e2.Position=Forms!AvgDialog![Position] Is Null) Or (e2.Position) Like
IIf(forms!AvgDialog!Text11="ALL","*",forms!AvgDialog!Text11)) And

(e2.DeptID=e1.DeptID And (e2.DeptID=Forms!AvgDialog!Dept) Or
(e2.DeptID=Forms!AvgDialog![Dept] Is Null) Or (e2.DeptID) Like
IIf(forms!AvgDialog!Text12="ALL","*",forms!AvgDialog!Text12))));



As previously mentioned this is OK! The problem I am having is with the query as follows. What I am basically trying to do is find out all the employees with an above average salary (including commission). Also, If the user does not enter any value, then all the records are used in the criteria. I think that is where the problem lies in the following query (the "Is Null" attributes).

I am using dreamweaver ASP Vbscript pages. I have a recordset called sal_max with the following query:

SELECT EmployeeID, LastName, FirstName, Salary, Commission, ([Salary]+[Commission]) AS [Total Salary], Position, tblBranch.BranchID, tblBranch.BranchName, tblDept.DeptID, tblDept.DeptName
FROM tblEmployees AS e1, tblBranch, tblDept
WHERE (e1.DeptID=tblDept.DeptID) AND (e1.BranchID=tblBranch.BranchID) AND ([Salary]+[Commission])>=(SELECT AVG ([Salary]+[Commission])
FROM tblEmployees e2
WHERE (e2.BranchID=e1.BranchID) And ((e2.BranchID=avgBranch) Or (e2.BranchID=avgBranch Is Null)) And (e2.Position=e1.Position And (e2.Position Like 'avgPos%')) And ((e2.DeptID=e1.DeptID
And ((e2.DeptID=avgDept) Or (e2.DeptID=avgDept Is Null)))))
ORDER BY ([Salary]+[Commission]) DESC

It appears to return all results I dont know why, I have looked over it many times and tried all sorts of trial and error but with no joy. As previously mentioned, I have a feeling the "Is Null" attributes are causing problems, but I do not know what! These are required so that if the user does not enter any criteria, all the records in that group (eg all towns) are used in the query.

I apologise for the long post, I know that it appears tedious. Thanks again to anyone that can help, Ive tried as much as I can *sigh*


Ricky S

Hi,

It would be useful if you could post the link to download a sample of the database so that we can see how the tables are structures and the relationships between the tables.

thanks