Filter rows based on date falling between two other dates

[Guest]

I have two tables. The first table includes a paycheck date. The other table
includes pay rate data and assigned job data with multiple start and end
dates for each time there is a rate or job change. I join the two via the
clock field (employee number). If the check date falls between the start and
end date of the pay rate data I want it to display the job associated with
start date and end date. Below is the current output of the query.

clock checkDate startDate endDate jobCode SumOfamount
242 7/5/05 5/28/02 10/24/05 220 1239.17
242 7/5/05 10/25/05 7/2/06 220 1239.17
242 7/5/05 7/3/06 12/31/2100 230 1239.17

I only want one row displayed which would be the first row because the
checkDate falls between the startDate and endDate on the same row. The
output would look like
242 7/5/05 5/28/02 10/24/05 220 1239.17

I'm trying to query what the current job was at the time the check was issued.

Thank you,

Scott

 

[Jeff L]

In the criteria of checkDate put, Between [StartDate] and [EndDate]

Hope that helps!

 

[Guest]

Thank you!

In the criteria of checkDate put, Between [StartDate] and [EndDate]

Hope that helps!

I have two tables. The first table includes a paycheck date. The other table
includes pay rate data and assigned job data with multiple start and end
dates for each time there is a rate or job change. I join the two via the
clock field (employee number). If the check date falls between the start and
end date of the pay rate data I want it to display the job associated with
start date and end date. Below is the current output of the query.

clock checkDate startDate endDate jobCode SumOfamount
242 7/5/05 5/28/02 10/24/05 220 1239.17
242 7/5/05 10/25/05 7/2/06 220 1239.17
242 7/5/05 7/3/06 12/31/2100 230 1239.17

I only want one row displayed which would be the first row because the
checkDate falls between the startDate and endDate on the same row. The
output would look like
242 7/5/05 5/28/02 10/24/05 220 1239.17

I'm trying to query what the current job was at the time the check was issued.

Thank you,

Scott