Find records based on dates in another table

[Paul Fenton]

I have a table of Sales Reps, each of whom has a Start Date and End
Date for their commission payout and the name of a company to which
that rep is assigned. A rep can have multiple records if he's serving
several different companies. I also have a table of Investigations
performed for these companies, each of which has a Completed Date.

A rep is eligible for a commission if an investigation for the company
he reps closes after his Start date and before his End Date, but not
eligible for investigations closed outside that range.

What I want to do is find each completed investigation for each rep
where the Closed date falls between the rep's Start and End dates for
his company.


Paul Fenton
(e-mail address removed)

 

[Michel Walsh]

Hi,


maybe something like:

SELECT a.*. b.*
FROM Sales As a INNER JOIN Investigations As b
ON a.SalesmanID = b.OurResponsibleID
WHERE b.ClosingDate BETWEEN a.Starting AND a.Ending



Hoping it may help,
Vanderghast, Access MVP