Query- a field appears multiple times, but different critera for each column

[t.p.lyons]

I'm working with a table of bird survival data I am trying to summarize in a query. I've got a bit of a roundabout way to achieve my goal, but I'm curious if there is a simpler approach.

Background:
In my table, each row represents a day I check a given nest and includes a [Nest ID] (not unique, multiple visits to each nest), a [visit ID] (auto numbered, so it's a unique value for each visit at each nest), the calendar day I visited a nest [Date], and [Survive] (1 or 0) depending on whether a nest survived or failed.

I'm trying to convert this detailed table into one that is more concise. Instead of each visit to a nest being a row, each nest becomes a row with 4 fields: The Nest ID, the minimum date (the day I found a nest), the last day a nest was checked (Max[Date]), and the last day a nest was checked alive (essentially max date where survival=1).

Problem:
My current solution is to run 3 separate queries. The first queries the max date where survival=1, the second queries the max and min dates regardless of any other criteria, and the third brings both queries together.

I am curious if there is a way to create the same final product in a single query rather than doing multiple ones as I have done? Any help or advice is appreciated! I apologize if this issue has already been addressed.

 

[rzw0wr]

Sounds like you need at least 1 more table.
You need a table for Nests.
A table for Visits. Related to Nests.
And maybe a table for Nest Details.

Dale

 

[t.p.lyons]

Hi,

Thanks for your response. I do in fact have a table that has only one row for each nest (NestID is the primary key) and data about where it was, the species, etc. It is linked to the table I previously mentioned, that describes each visit to a nest. There are actually a host of tables linked to these as well, ones concerning vegetation measurements, measurements of nestlings etc.

I initially limited the background information to try and stay on point with the query issue I was having, I guess I should have provided more detail!

 

[JHB]

Do you have same sample data, (post it in a Excel sheet or database, zip it because you haven't post 10 post)?