By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,178 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Access Query with IF THEN statements

P: 4
What I am trying to do is create a query in Access that will help in my Worker's Comp reporting. I already have queries set up but I am missing one component. I have a query that states IF payrollold is greater than 750 THEN 750 IF not THEN payrollold. However I need to add a twist in that I need to add a statement which would give me the following result: IF hours is greater then 80 THEN 1500 (in the payroll column) IF not then 750.

I am having trouble mixing the hours part with the payroll part. Below is the SQL view of the query I need to modify.

SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION], IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")

Any help would be greatly appreciated. Thanks Lou
Jul 24 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 374
Expand|Select|Wrap|Line Numbers
  1. SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION], IIf([payrollold]>750,750,[payrollold]) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
  2. FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
  3. WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")
You would write is like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT ADPINTER.JOB_NUMBER, ADPINTER.EMPNO, ADPINTER.[EMPLOYEE NAME], [reg_hrs]+[ovr_hrs]+[other_hours] AS hours, EMPMAST.RATE1AMT, [hours]*[rate1amt] AS payrollold, EMPMAST.WORKERSCOMPCODE, ADPINTER.ENDPERIOD, 1 AS [COUNT], ADPINTER.[UNION],IIF([reg_hrs]+[ovr_hrs]+[other_hours]>80,1500,IIF([reg_hrs]+[ovr_hrs]+[other_hours]<=80 and [PayrollOld]<=750,[PayrollOld],750)) AS Payroll, ADPINTER.[total pay], ADPINTER.category, [Reg_grs]+[ovr_grs]+[Other Gross] AS gross
  2. FROM EMPMAST INNER JOIN ADPINTER ON (EMPMAST.[CO CODE] = ADPINTER.COMPANY) AND (EMPMAST.EMPNO = ADPINTER.EMPNO) AND (EMPMAST.EMPNO = ADPINTER.EMPNO)
  3. WHERE (((ADPINTER.JOB_NUMBER) Between [forms]![autoload]![From Job] And [forms]![autoload]![To Job]) AND ((ADPINTER.ENDPERIOD) Between [forms]![autoload]![STARTDATE] And [forms]![autoload]![ENDINGDATE]) AND ((ADPINTER.category)<>"GC")
Jul 25 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.