Filter out table for latest/best collar

[Eskimo]

Hi All,

I have a linked table, which has information about caribou movement. 4 of
those columns are important to this question. They are Collar(the collar
number), Date(Date when a position was "Fixed"), Time (Time of day when the
fix occured), and LC(Location Class, indicator of Fix quality from 1-7 with 7
being the highest).

I need a query that will filter the data to provide me with a record for
each collar that is the latest (in terms of time) and best (In terms of LC)
for each day.

There are multiple collars. Each collar has multiple records on the same
day. Each collar also has records on seperate days.

If anyone can help, thanks very much.

Eskimo

 

[Dale Fye]

Eskimo,

As long as you recognize that the Best and Latest values may not (probably
don't) belong to the same observation, you should be able to use:

SELECT Collar, Date, MAX(Time) as Latest, Max(LC) as Best
FROM yourTAble
GROUP BY Collar, Date

HTH
Dale

BTW, Date and Time are both functions used within access and should not be
used as field names. I would change the names to Obs_Date, and Obs_Time.
Actually, I prefer to keep these two values in a single Date/Time field, but
that is a matter of preference.

 

[Eskimo]

Hi Dayle,

The code worked but gave a prompt for which collar to enter so I made the
code like this....

SELECT Collars.Collar, Collars.Date, Max(Collars.Time) AS Latest,
Max(Collars.LC) AS Best
FROM Collars
GROUP BY Collars.Collar, Collars.Date, Collars.Collar

However, the code now has the right collar with the highest rank, but the
coresponding time is wrong in that it took out the max time for that group of
collar, regardless of which rank the collar is retreiving from. I don't know
if you understand that, but right collar from the right record, right LC
which is the best rank from that same record, but wrong time in that it just
reported the max time for that group of collars. Even though the max timed
record does not have the best LC.

Eskimo

 

[Dale Fye]

That is why I put the caveat in my reply. What you asked for was a record
for each collar/date that contained the latest time for that collar/date and
the best reception. You did not ask for the latest record for each
collar/date combination that had the best reception.

If that is what you want, you will need to do something like:

SELECT C.Collar, C.Date, MAX(C.Time) as Latest, C.LC as Best
FROM Collars C
WHERE C.LC = (SELECT MAX(LC) FROM Collars
WHERE Collars.Collar = C.Collar AND Collars.Date =
C.Date)

Does that do what you want?

Dale

 

[Eskimo]

Hi Dale,

My english is improvement over the years and I start to wonder about how I
am going to word a complex idea such as this. Forgive me. I though it was
pretty clear that it needs to be the best record which is also the latest.
Anyways, my mistake and thanks for clarifying for those reading.

I did end up with a solution like this..Thanks to duke carey over at excel
worksheet functions. he was the one suggesting I try linking and using
access when the original file was in Excel. Here is his solution.

SELECT c1.Date, MAX(c1.Time) AS MaxTime, c1.Collar, c1.LC
FROM Collars AS c1 INNER JOIN [SELECT Date, Collar, Max(LC) as MLC from
Collars group by Date, Collar]. AS c2 ON (c1.LC=c2.MLC) AND
(C1.Collar=c2.Collar) AND (c1.Date=c2.Date)
GROUP BY c1.Date, c1.Collar, c1.LC;