Query to select first record of changes between records

[Guest]

Not sure how to explain my question, but perhaps some background on my
database will help. I'm putting together a database where employees use a
form to enter the status of various seabird nest sites that we are monitoring
for a restoration project. Nests are monitored everyday, so each day a code
is entered into the form indicating the status of the nest site on that day
(e.g B/0=bird over nothing, B/E= bird over egg). During the course of the
year we will want to query this data to determine the first day that eggs
were seen at each nest. I'm trying to figure out a query that will, at the
very least, select the very first record that contains an "*E*" in the status
column. Unfortunately, I can't use *E* b/c that gives me the two codes B/E
and 2B/E. I need something that can pick out just the first record that
contains E in the site status column. In a perfect world, it would be better
to have a query that could select the first record that has an E in it after
a string of records that do not contain an E. This way, I can determine the
date that eggs were first seen in nests that have more than one breeding
attempt in a seaon (e.g. nest 1 is B/E on the first, loses this egg and has
two weeks of B/0 and then another B/E). Thanks a bunch.

 

[Amy Blankenship]

In a perfect world, you'd have a statuses table, so the different statuses
would have a different primary key and they'd be very clearly different.
You haven't really explained why 2B/E is different and why it would be
undesirable to get that record, too.

However, you might want to try something like

SELECT MIN(RecordID) FROM YourTable GROUP BY SiteID HAVING StatusCode LIKE
'*E*'

I'm not sure how you'd get the second attempt at nesting. Maybe you should
flag each "brood" with an ID. Then it would be very easy to determine the
day on which the eggs of the brood were first spotted.

HTH;

Amy

 

[Guest]

Hi Amy,

For a moment, it is a perfect world. I do have a status table for the nest
status! B/E = 6 and 2B/E = 3 and they are primary keys. Your right it isn't
so important that it is B/E or not. However, I'm calculating expected hatch
date from the first time an egg was seen, so if I just run a query that finds
B/E or 2B/E I often get two records for everynest, when I really want the
first time an egg is seen.

 

[Guest]

Hey Amy,

Your clue to site status ID sparked me on the right path! Got the query to
work and eliminated several queries in the process! Thanks a bunch!

 

[Amy Blankenship]

You're welcome

Hey Amy,

Your clue to site status ID sparked me on the right path! Got the query
to
work and eliminated several queries in the process! Thanks a bunch!