Query to calculate correct rate 3

[jfussell]

I'm at a loss with my query and don't know what to do; any suggestions would be greatly appreciated. Here's what I have:

1 table that consists of employee info, columns are

Employee | Rate1 | Rate2 |
Rate1 and Rate2 differentiates the salary after raises (hourly rates).

Another table consists of 2004 timesheets, columns are

Project | Date | Employee | Duration | Rate(combo box of Rate1 and Rate2, which shows which rate to use for calculation purposes)

I would like the query to calculate the project costs for the year, which would be duration x rate, but how should the expression be setup for it to know that if the combo box lists Rate1 to look for Rate1 in the Employee table? Thanks for any input on this.

 

[PHV]

What are the stored values in your table for the field Rate ? 1,2 ? "Rate1","Rate2"
If numeric values like 1 or 2, you may try something like this:
SELECT T.Projects,Sum(T.Duration*IIf(T.Rate=1,E.Rate1,E.Rate2)) As Cost
FROM tblTimeSheets T INNER tblEmployee E ON T.Employee=E.Employee
WHERE Year(T.Date)=2004
GROUP BY T.Projects;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jbpez]

I like to create functions for complex situations like these.

Function AppropriateRate(varField as variant, varEmployee as variant) as variant

 Dim varRate as variant

 varRate = DLoopUp("[" & varField & "]", "EmployeeTable", "[Employee]=" & varEmployee)

 AppropriateRate = varRate

End Function

Then on your SQL use this function:

SELECT Projects, Date, SUM([Duration]*AppropriateRate([Rate],[Employee]))
FROM TimeSheetsTable
GROUP BY Projects, Year([Date])

John Borges

 

[dhookom]

I would normalize the table so that I didn't have Rate1 and Rate2. Each rate for each employee should be in its own record in a related table. This makes querying quite simple.

I would also no use DLookup() in a query or report since it creates a recordset with each call.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jfussell]

Thanks to everyone that has posted and given me suggestions. However, I decided to take the easy way out and went with dhookom's suggestion; and now my query works!! But I do want to leave a thank-you note to PHV and jbpez for your time helping me with my problem. Everyone gets a star for the effort!
Thanks again. LOVE TEK-TIPS!

Judy