Multiplication when certain criteria is met!!

[Dineedshelp]

Help!!!

I have a problem with time!!!!
I an writing a query and basically I am trying to work out how much we are spending depending upon when the person works!!!

I have columns, start time, finish time and hours worked, what I want to do is combine a separate database to this with various rates for days and nights, this I can do, but each employee has a category into which they fall, each category has different rates for days, nights, weekends and holidays, and depending upon the category different start and finish times for nights and days.

How can I get it to pick the right category of rates for each employee, and then the correct times, bearing in mind that they may be paid for half and hour at night rate and the rest at day rate.

ie, Joe B works Mon, Tue, Wed, Sat and Sun, starting at 7.30am and finishing at 5pm, breaks are paid for.
His category is A, the rates for weekday day start at 9am, before this needs to be weekday night rate, same times for the weekend but different rates.

Can ANYBODY please help????

 

[dreameR25]

Give us some sample data structure and some sample data and we will be able to help you with this problem.

 

[Dineedshelp]

OK, here goes.

Basically pulling info from three tables

EmployeeTable: (Holds shifts that employees has done)

Cat ShiftDay ShiftDate EmpGrade Start Finish EmpNo
X MON 17/11 A 7.00 15.00 216
X TUE 18/11 B 7.00 15.00 216
X WED 19/11 A 7.00 15.00 216
Y SAT 22/11 C 7.00 15.00 216
Z SUN 23/11 C 7.00 15.00 216

CategoryRatesTablevarious rates for each category)

Cat EmpGrade DayRate NightRate Weekend weekend
Day Night
X A 10.00 12.00 14.00 16.00
Y A 12.00 14.00 16.00 18.00
Z A 14.00 16.00 18.00 20.00

X B 12.00 14.00 16.00 18.00
Y B 14.00 16.00 18.00 20.00
Z B 16.00 18.00 20.00 22.00

X C 14.00 16.00 18.00 20.00
Y C 16.00 18.00 20.00 22.00
Z C 18.00 20.00 22.00 24.00

CategoryTimeTable: (start and finish times for rates)

Cat DayStart NightStart WeekendDaySt WeekendNightSt
X 8.30AM 6.30PM 8.30AM 8.00PM
Y 9.00AM 5.00PM 7.30AM 9.00PM
Z 7.30AM 7.00PM 6.30AM 7.30PM

Do you see my problem??!!!
I need to work out what EmpNo 216 has cost from using the information in the employee table, and the two other tables.
PLEASE tell me you can help!!!!