refer to another field?

[Mcgr0199]

Hi, I'm not sure how to explain this question clearly, but I'll do my best. I'm trying to query a table for dates and times greater than the dates and times specified in another table. I have multiple animals that were GPS-collared on different dates and times, but the collars were turned on before the animals were captured, so I can't just query the first date/time that each collar recorded because it wasn't on the animal yet. What I want to be able to do is query out every location of each animal (64 individuals) after the time the individual was collared. Capture dates and times (one for each animal) are in one table while GPS locations from each animal/collar (thousands for each) are in a different table. Any ideas on how to write that command? Thanks!

 

[John Big Booty]

I think you will first need to create a query that gives you the capture/collar date for each animal. You can then use the results in that query as part of your second query that will return all date greater that the capture date.

 

[Mcgr0199]

Thanks, I've already created the query to get the capture/collar dates. I guess my question is how do I write the command that will give me all of the dates/times post capture in the second query? Will it be something like... >[the capture date field]?

 

[jdraw]

I'm not sure what your table structures look like. I'm guessing you have 2 tables???
However, I see it as 3 tables

Animal(AnimalId,AnimalName,......)
AnimalCapture(AnimalId,CaptureDate,....)
AnimalLocs(AnimalId,SightingDate,GPSLat,GPSLong,.....)


Query: Animal and sighting details post capture

SELECT Animal.AName, AnimalLocs.SightingDate, AnimalLocs.GPSLat, AnimalLocs.GPSLong
FROM (Animal INNER JOIN AnimalCapture ON Animal.AnimalId = AnimalCapture.AnimalId) INNER JOIN AnimalLocs ON Animal.AnimalId = AnimalLocs.AnimalId
WHERE (((AnimalLocs.SightingDate)>[AnimalCapture]![CaptureDate]))
ORDER BY Animal.AName, AnimalLocs.SightingDate;