Query with Date Dependencies

[Eskimo]

In my tblMaster, I have fields that include CollarNumber and FixDate along
with other fields. This table shows the location of a satellite collar on
caribou for given days.

I then have tblDeployments that has the fields AnimalID, CollarNumber,
DateDeployed, DateEnded. This table shows a list of caribou that were fitted
with collars, when they were put on and when the last day of service was for
that animal. Now, a single Animal can be re-captured with a different Collar
and also a Collar recovered can be re-furbished and fitted to another Animal.
So the list has mostly a single animal and a single collar, but there are
many lines of the same AnimalID with different Collars, and also the same
CollarNumber on many AnimalIDs.

I am hoping to get help on a query design where I would Select tblMaster.*,
then include the corresponding animalID where the CollarNumber and the Date
fit the AnimalID from tblDeployments

Tia

 

[John Spencer]

Use a non-equi join to get the results. The query would look something like
the following. You cannot build this query in design view, but must use the
SQL view.

SELECT M.*, D.AnimalID
FROM tblDeployments as D INNER JOIN tblMaster as M
ON D.CollarNumber = M.CollarNumber
AND D.DateDeployed <= M.FixDate
AND D.DateEnded >= M.FixDate

Actually, you can build MOST of the query in design view.
-- Add both tables
-- Add the fields you want to see
-- Join Collar Number to CollarNumber
-- Join DateDeployed to FixDate
-- Join DateEnded to FixDate
-- Select VIEW SQL from the menu
-- Find the on clause and change the = to <= and >= as appropriate between
DateDeployed and FixDate and between DateEnded and FixDate. If the results
don't appear to be correct try switching the <= and >= around.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Eskimo]

Hi John,

Thanks for the reply, it worked beautifully!