Running different queries within expression based on field value

ClintonHolder

New member
Local time
Today, 16:37
Joined
Jan 29, 2012
Messages
3
I have a points based attendance database that I am trying to enhance with different rules based on if the employee is part time or full time, for instance I am trying to Dsum their points from all occurrences within the last 365 days but if now I am trying to create some sort of IF the "employeestatus" = PT then only dsum from after Jan 1, 2012 since part time employees had a policy change and I don't want to tally points before the 1st of this year. Can anyone help me understand how to change this expression or allow me to convert it to an IF THEN ELSE statement where I can run different queries based on the value of the "employeestatus" field?

This is what I have in the control source for the text box on a form that totals the employees current points.
Code:
=DSum("Points","qryTardy","EmployeeID = " & [txtEmployeeID])
qryTardy is the query that filters all occurrences in the last 365 days
I want the ability to run qryTardyPT if the "employeestatus" field = PT

qryTardy:
Code:
SELECT tblEmployeeTardy.EmployeeTardyID, tblEmployeeTardy.Date, tblEmployeeTardy.EmployeeID, tblEmployeeTardy.Hours, tblEmployeeTardy.Minutes, tblEmployeeTardy.Comments, tblEmployeeTardy.Points, tblEmployeeTardy.TardyID FROM tblEmployeeTardy WHERE (((tblEmployeeTardy.Date)>=Date()-365));

Sorry if this sounds convoluted, I have a hard enough time wrapping my brain around it asa semi beginner to Access
 
Howzit

How about this for qryTardy. This returns everything for FT emps no later than a year preior, but for PT emps this limits it to everything after 01/01/12 and as long as it is less than a year old.

Your DSum will not need to be changed.

Code:
qryTardy:
Code:
SELECT tblEmployeeTardy.EmployeeTardyID, tblEmployeeTardy.Date, tblEmployeeTardy.EmployeeID, tblEmployeeTardy.Hours, tblEmployeeTardy.Minutes, tblEmployeeTardy.Comments, tblEmployeeTardy.Points, tblEmployeeTardy.TardyID 
FROM tblEmployeeTardy 
WHERE (((tblEmployeeTardy.Date)>=Date()-365) AND ((tblEmployeeTardy.EmployeeStatus)='FT')) OR (((tblEmployeeTardy.Date)>=Date()-365) AND ((tblEmployeeTardy.EmployeeStatus)='PT') AND ((tblEmployeeTardy.Date)>=#1/1/2012#));
 
Thanks Les, The query looks like it works by itself but for some reason the text box's control source that runs the query in the expression just returns #ERROR

Any ideas why?

Code:
=DSum("Points","qryTardy","EmployeeID = " & [txtEmployeeID])
Is it because the DSum has no idea if the txtEmployeeID is PT or FT? Because when I just run the query stand alone it asks me what the EmployeeStatus is...

I uploaded the db, the error I am referring to is on the frmMain where each employee is listed and under the points column
 

Attachments

Last edited:
Howzit

You need to join to the employee table. Replace your current sql for qryTardy with the below

Code:
SELECT tblEmployeeTardy.EmployeeTardyID, tblEmployeeTardy.Date, tblEmployeeTardy.EmployeeID, tblEmployeeTardy.Hours, tblEmployeeTardy.Minutes, tblEmployeeTardy.Comments, tblEmployeeTardy.Points, tblEmployeeTardy.TardyID
FROM tblEmployee INNER JOIN tblEmployeeTardy ON tblEmployee.EmployeeID = tblEmployeeTardy.EmployeeID
WHERE (((tblEmployeeTardy.Date)>=Date()-365) AND ((tblEmployee.EmployeeStatus)='FT')) OR (((tblEmployeeTardy.Date)>=Date()-365) AND ((tblEmployee.EmployeeStatus)='PT') AND ((tblEmployeeTardy.Date)>=#1/1/2012#));
 
Howzit

The intial query I gave does not work, as I assumed that the employee status was in the same table as the rest of the fields- whereas it is in a different one - the main employee table. This required a join for it to work
 
Thanks Les,

Before this I didn't know how you could stack the criteria, your solution works perfectly! You saved me many hours of trial and error! Thanks again!
 

Users who are viewing this thread

Back
Top Bottom