Create query that returns results based on criteria in another table 1

[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?

 

[MajP]

You probably need to document your tables to get a good answer. Either do it manually or you can use this code
FAQ700-6905.

Without seeing your tables it will be hard to say. I see references to dates but in the singular. Usually there is a start and stop date (e.g. dtmTripStart, dtmTripEnd, dtmRateStart, dtmRateEnd)

Also it is usually considered bad etiquette to post the same thread in multiple forums, but this is a "query" specific thread and should be in the Access Query and JET SQL forum. If you repost in the query forum just state that it is a repost and reference the thread number. Do the same in this forum and tell people to respond to the "query" thread. Include the thread number . Most Access responders cruise all the forums, but this one is a little less reviewed than the Access Queries and Jet SQL forum.