anybody not use caculation

[John Vinson]

THIS IS WONDERFULLLLLLL!!! It is working exactly the way i wanted. one more
question, to make my administrator user easier in the future, is there a way
that I can set up something and she can change the expression of greater than
or less? The amount can be changed depends on the company. Is there a way
she can change it without going into Query View?

You can change the cutoff value from a hardcoded 2000 to let the user
enter a value: it's probably best to use a Form for this purpose,
rather than a prompt. Create a little unbound form named frmCrit with
textboxes txtCutoff and txtYear:

SELECT TBLEmp.<whatever fields you want>, Sum(tblCost.Cost)
FROM (TblEmp INNER JOIN tblCourseInfo
ON tblEmp.EmpID = tblCourseInfo.EmpID)
INNER JOIN tblCost
ON tblCost.CourseID = tblCourseInfo.CourseID
GROUP BY tblEmp.EmpID
HAVING Sum([tblCost.Cost) > [Forms]![frmCrit]![txtCutoff]
WHERE Year(tblCourseInfo.CourseDate) = [Forms]![frmCrit]![txtYear];

If you want to toggle between searching for sums greater than a cutoff
and searching for sums less than the cutoff, it's a bit more work. You
might do best to have two separate queries and use one or the other
based on the user's choice on frmCrit.

John W. Vinson[MVP]

 

[accessuser via AccessMonster.com]

John,

I appreciate all your help! I created a form with your code and also use it
for the reports. It's working so well now. . It is amazing how one right
query can make the entire database useful.

Thank you again!

THIS IS WONDERFULLLLLLL!!! It is working exactly the way i wanted. one more
question, to make my administrator user easier in the future, is there a way
that I can set up something and she can change the expression of greater than
or less? The amount can be changed depends on the company. Is there a way
she can change it without going into Query View?

You can change the cutoff value from a hardcoded 2000 to let the user
enter a value: it's probably best to use a Form for this purpose,
rather than a prompt. Create a little unbound form named frmCrit with
textboxes txtCutoff and txtYear:

SELECT TBLEmp.<whatever fields you want>, Sum(tblCost.Cost)
FROM (TblEmp INNER JOIN tblCourseInfo
ON tblEmp.EmpID = tblCourseInfo.EmpID)
INNER JOIN tblCost
ON tblCost.CourseID = tblCourseInfo.CourseID
GROUP BY tblEmp.EmpID
HAVING Sum([tblCost.Cost) > [Forms]![frmCrit]![txtCutoff]
WHERE Year(tblCourseInfo.CourseDate) = [Forms]![frmCrit]![txtYear];

If you want to toggle between searching for sums greater than a cutoff
and searching for sums less than the cutoff, it's a bit more work. You
might do best to have two separate queries and use one or the other
based on the user's choice on frmCrit.

John W. Vinson[MVP]

 

[John Vinson]

John,

I appreciate all your help! I created a form with your code and also use it
for the reports. It's working so well now. . It is amazing how one right
query can make the entire database useful.

Delighted to have been of assistance!

John W. Vinson[MVP]