Query using multiple criteria to populate a new column

[Alecia]

Hello, I am not familiar with Access and was asked to create a db for attendance. I thought I was done and then was asked to add a column to calculate the number of occurrences by employee, simple enough or so I thought. I have a table which has employee names, time in, earning code and total hours for each line. So assume Employee A worked from 8:00a- 12:00p and the earning code is "Regular" with 4 listed in the total hours for that line. Now, Employee A left early for the rest of the day so the next row would look like Employee A- 12:30p-4:30p- "Unpaid Time" (as the earning code) and 4 for total hours. The 4 hours coded as "Unpaid Time" should result in 0.50 of an occurrence.



What I need is another column added now named 'Occurrences' which is going to calculate all rows that contain the earning code " Unpaid Time" and look at the number of hours to determine how much of an occurrence this will be. So the occurrences are calculated in quarters based the number of hours.

0.5 minutes to 2.00 hours = 0.25 of an occurrence
2.01 to 4.00 = 0.50 of an occurrence
4.01 to 8.00 = 1 occurrence

I am so lost someone please help, thank you!

[orange]

Alecia,

We all started with what was described as a simple straightforward database -or so we thought/were told.

there is more to database that a single table. There are some links in this post that deal with database design and planning. I recommend the tutorials and videos highly.

Good luck.

[ranman256]

I normally make a query, Q1SumEmpDay, to add up time for each record.
select emp, WorkDate: format([StartTime],"short date") , TimeWorked: datediff("h",starttime,endtime)

then Q2SumHrs, sums the REG time for Emp/Day
select Lbl: "EmpTime",emp, workdate, Sum(TimeWorked) from Q1SumEmpDay

then Q3Reg gets only reg hrs ,
select Lbl: "RegHrs",emp workdate ,RegHrs: IIF(SumTimeWorked>40,40,SumTimeWorked) from Q1SumEmpDay

then Q4OT gets only overtime ,
select Lbl: "OT Hrs",emp workdate ,OTHrs: IIF(SumTimeWorked>40,SumTimeWorked-40,0) from Q1SumEmpDay

then get the missing hrs
select Lbl: "missing hrs", emp workdate ,MissingHrs: IIF(SumTimeWorked<40,40-SumTimeWorked ,0)

put them all together in a UNION query.

[June7]

Expression in query to calculate unpaid hours Occurrence:

IIf([EarningCode]="Unpaid", Switch([TotalHrs]<=2,0.25, [TotalHrs]<=4,0.50, [TotalHrs]<=8,1, True,0), Null)

or

IIf([EarningCode]="Unpaid", Switch([TotalHrs]>4,1, [TotalHrs]>2,0.50, [TotalHrs]>0,0.25, True,0), Null)

[Alecia]

Thank you all for your help, I am still getting errors that expressions is to complex. I think I add another column in my table that hold this data and continue using excel to calculate it, so when I do my weekly upload to the DB it is already done and the supervisors can just run the report from the DB. My excel file was getting too large.

Thank you again!