Re: Query to select first record of changes between records
- From: "Amy Blankenship" <Amy_nospam@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 27 Feb 2006 13:34:27 -0600
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
"peter kappes" <peterkappes@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B47DD28E-540C-4B41-938B-4C1B3F9F92FA@xxxxxxxxxxxxxxxx
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.
.
- Follow-Ups:
- Re: Query to select first record of changes between records
- From: peter kappes
- Re: Query to select first record of changes between records
- Prev by Date: Re: Need to Create a Query
- Next by Date: Re: Decimal fields's precision is too small to accept the numeric you attempted to add
- Previous by thread: Random
- Next by thread: Re: Query to select first record of changes between records
- Index(es):
Relevant Pages
|
