Repost - Need to create query that reference criteria in another table 3

[AccessQween]

I am desperately trying to calculate total labor for employees in one table based on their rate of pay during the time period worked. I have a table that has the date of trip, and number of hours. I have another table that has the employee pay history which is the rate date and the new rate of pay. Both of these tables are joined using the employID field. If an employee received three pay raises in a year how can make sure that rate of pay for each trip matches based on their rate of pay at that time? Please reply to: thread181-1474933


I have three tables

tbl_Employee
EmployID
Name
Address

tbl_DailyTracking
TripDate
EmployID
Hours
RouteNo

tbl_EmployeePayHistory
EmployID
RateStartDate
RateEndDate
NewRate

Relationships
The Employee table has a one to many relation with the Daily Tracking table and the Employee Pay History Table.

I tried to use the following if statement but I get multiple results for each record. I can't figure how to calculate the Total Labor using the appropiate pay rate for the paticular trip date.

Total Labor: (IIf(IsNull([Employee Rates].RateEndDate),[DAILY TRACKING].Stipend*[Employee Rates].NewRate,IIf([DAILY TRACKING].tripDate>=[Employee Rates].RateStartDate And [DAILY TRACKING].tripDate<=[Employee Rates].RateEndDate,[DAILY TRACKING].Stipend*[Employee Rates].NewRate)))

What am I missing or doing wrong.

 

[fneily]

I hope these tables are not written in stone. Some reading:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You say you have a one-to-many relationship between tbl_Employee and tbl_EmployeePayHistory. The only way, as presented, that could happen is that tlb_EmployeePayHistory has no primary key. Or do you have a multi-field PK? But that would negate two trips on the same day.
What's the primary key for the tbl_DailyTracking?

Also, I guess from your SQL that EmployeeRates is actually tbl_EmployeePayHistory.
And [Daily Tracking] is actually tbl_DailyTracking. And Stipend is in tbl_DailyTracking.
What a mess.

Why don't you try this:
tblEmployee
EmployeeID Primary Key
FirstName
LastName
other fields for employee

tblPayHistory
PayHistoryID Primary Key
EmployeeID
StartDate
EndDate
Rate

tblDailyTracking
DTID
EmployeeID
PayHistoryID
Date
Hours
RouteNo.

Now you just connect the tables in a query and do a straight muliplication.

 

[AccessQween]

Thanks fneily. This helps a lot but I have a question. If I reference the PayHistoryID in the Daily Tracking table does that mean that each time I enter a record I have to type in the appropriate PayHistoryID for that record or is there a way to automate this process?

 

[Golom]

You're missing some clauses in your IIF statements

IIf(IsNull([Employee Rates].RateEndDate), 
    [DAILY TRACKING].Stipend*[Employee Rates].NewRate,
        IIf([DAILY TRACKING].tripDate>=[Employee Rates].RateStartDate 
            And [DAILY TRACKING].tripDate<=[Employee Rates].RateEndDate, 
            [DAILY TRACKING].Stipend*[Employee Rates].NewRate
            [red], Clause if it isn't between those dates[/red])
    [red],Clause if 'RateEndDate' IS NOT NULL[/red])

You could make it work as you have it by making "EmployID + RateStartDate" the primary key for "tbl_EmployeePayHistory".
Presumably no employee will have their salary adjusted twice on the same day.

Select E.EmployID, SUM(T.Stipend * P.NewRate) As [Total Labor]

From (tbl_Employee As E INNER JOIN tbl_DailyTracking As T
      ON E.EmployID = T.EmployID)

     INNER JOIN tbl_EmployeePayHistory As P
     ON E.EmployID = P.EmployID

Where T.TripDate BETWEEN P.RateStartDate AND P.RateEndDate

Group By E.EmployID

 

[fneily]

To answer your question, usually if you're referencing data from a field in another table, for ease of data entry, you'd create a dropdown(combobox). This way the user selects and doesn't type. Think about it, the leass typing, the less typing errors. No typing, no errors. This would also apply to EmployeeID.
Dates can be click on from a calendar. Again, no typing.

So your combobox for PayHistoryID could be based on a query that shows Max date for each employee, the latest raise or cut in salary.

 

[AccessQween]

Thanks guys. I will take a stab at it again.

 

[Golom]

Just to be safe

Select E.EmployID, SUM(T.Stipend * P.NewRate) As [Total Labor]

From (tbl_Employee As E INNER JOIN tbl_DailyTracking As T
      ON E.EmployID = T.EmployID)

     INNER JOIN tbl_EmployeePayHistory As P
     ON E.EmployID = P.EmployID

Where (T.TripDate BETWEEN P.RateStartDate AND P.RateEndDate)
   [red]OR
      (T.TripDate >= P.RateStartDate AND P.RateEndDate IS NULL)[/red]

Group By E.EmployID

Presumably the current salary will not yet have a "RateEndDate" value assigned yet.

 

[AccessQween]

That's correct and I that is what I was trying to accomplish in my IF statement. Thanks so much Golom.

 

[AccessQween]

I tried using the code above and I keep getting an error - stating that it is in the from statement and "As" in the second line before the T is highlighted. I am not that familar with code to troubleshoot this. Any assistance would be greatly appreciated.

 

[Golom]

What is the exact error message?

Two possibilities ...

Your tables are actually in some other database such as Oracle and this is being used as a passthrough query. Oracle doesn't support the "As" keyword.

You retyped the statement (rather than copy and paste) and didn't get it quite right.

Can you copy and paste the SQL you are running so that we can have a look at it?

 

[AccessQween]

Hi Golom,

I copied and pasted the code and it worked! I have a question though - in the code am I supposed to include the OR statement or use one or the other? I removed the statement before the OR.

 

[AccessQween]

Scratch the last question - I answered it. But now I am having another problem. The code is working but know I am trying to create a form so that when the data is entered it would calculate the labor like it suppose to. How do I set up my form so that new records can be added. Right know it's just giving me the results of the query in my form but I can't make any changes or add any records.

 

[Golom]

That's a new issue. I suggest that you start a new thread in forum702.

 

[PHV]

Have a look here:

http://support.microsoft.com/kb/148581

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AccessQween]

Hi Again,

Golom I just want to say thank you for your help. I wouldn't have ever figured it on my own.

PHV

Thanks for the links.