Comparing Dates to Get the Right Rate

[froggiebeckie]

I've done some searching and came close, but can't figure out how to query this.

Large db with multiple tables, but simplified:
WrkdHrs table, holds individuals weekly hrs, etc with "PeriodEnd" date field.
Raise table, holds pay rate for individuals, with "Effective" date field.

I need to pull the hours worked records and show the pay rate relavant to that time period.

(PeriodEnd 03/12/12 Johnny Jumpup worked 10 hrs and was paid $12 per hr.)

The problem is, Johnny may have gotten a raise since then, so I need to know what the rate was during that time frame.

I've tried to add a field to the query with something like:
PayRate: (SELECT Top 1 PayRate FROM Raise WHERE Effective <=PeriodEnd AND WrkdHrs.SS=Raise.SS)

This seemed to work, but it gave top rate, ever, not the rate in effect during that time period.

Am I close?
Do I need to break it into 2 different queries?
Any ideas, folks?

Thanks in advance,
Beckie

 

[spikepl]

"Top 1" grabs one record fulfilling the criteria out of an - here - unspecified sequence. Tables have no inherent sequence/order. You need to add ORDER BY something, presumably Effective

 

[froggiebeckie]

Thanks so much--I'll get right on it and let you know how it goes.