Adding Columns in a Query don't work

[Guest]

I have a table with 21 columns, 4 of which I use in the following query
statement that gives me a sheet with all of the collars which have the best
signal, which is also the latest signal, on that particular day. But I need 3
other columns that provide the Latitude and Longitude and Activity Level for
each of the records showed up in the sql statement.

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

Out of the 21 columns, the four used in the SQL Statement above are Collar,
Obs_Date, Obs_Time and LC. Collar is Collar Number, Obs_Date is the date,
Obs_Time is the time and LC is an indicator of Position fix quality (possible
values are 1 to 7 with 7 being highest).

The above statement gives me the proper records I am looking for, but I am
having great difficulty with the adding of the columns LAT (Latitude), LON
(Longitude) and ACT (Activity level) into the sql statement.

The trouble comes when I add those columns, it gives me more records than
what I need. It ends up giving me all of the best LC's regardless of what
time it reported.

I need to be able to only to retrieve the best ranked LC for each collar for
each day, but since there can be more than one best ranked LC for each collar
on that day, I need to filter so that I can get the latest ranked record on
that day.

Hopefully you can help me out here.

Eskimo

 

[Guest]

Use two queries. First one pulls the max and second uses it and the table
joined to display the rest of the information.

Why Max(c1.Obs_Time) AS MaxTime?

 

[Guest]

Hi Karl,

The simple use of two queries does not work. It provides too many records
that are redundant. Also, there are records in the query that don't match
what is available from the Collars table.

Perhaps I should start again with a recap.

Collars
--------
ID (PK, autonumber)
Collar (number) has multiples of the same collar
Obs_Date (Date, at which a Fix on the position was had)
Obs_Time (Time, at which the fix on the position was had)
LC (Number, Rank from 1-7 with 7 being highest)
Lat (Text, ##.###a ie. 63.249N the Latitude of the fix)
Lon (Text, ##.###a ie 95.567W The longitude of the fix)
ACT (Number, Activity sensor on collar that tells head/Neck Movement)

The query below gives me the maximum LC for each collar on each day. I need
the MaxTime to give me the latest fix on that day that was also the highest
LC. Because there is a possibility that any given collar on any given day
will report two or more records that have the same high ranking LC, but at
different times. So I need Max time to give me the latest/best LC)

I think what we need to do is first combine the collar and date as a group.
the from each group, find the highest LC's. Then from that group with the
Highest LC's, find the latest.

Any help with suggested SQL's would be greatly appreciated.

Eskimo

Use two queries. First one pulls the max and second uses it and the table
joined to display the rest of the information.

Why Max(c1.Obs_Time) AS MaxTime?

I have a table with 21 columns, 4 of which I use in the following query
statement that gives me a sheet with all of the collars which have the best
signal, which is also the latest signal, on that particular day. But I need 3
other columns that provide the Latitude and Longitude and Activity Level for
each of the records showed up in the sql statement.

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

Out of the 21 columns, the four used in the SQL Statement above are Collar,
Obs_Date, Obs_Time and LC. Collar is Collar Number, Obs_Date is the date,
Obs_Time is the time and LC is an indicator of Position fix quality (possible
values are 1 to 7 with 7 being highest).

The above statement gives me the proper records I am looking for, but I am
having great difficulty with the adding of the columns LAT (Latitude), LON
(Longitude) and ACT (Activity level) into the sql statement.

The trouble comes when I add those columns, it gives me more records than
what I need. It ends up giving me all of the best LC's regardless of what
time it reported.

I need to be able to only to retrieve the best ranked LC for each collar for
each day, but since there can be more than one best ranked LC for each collar
on that day, I need to filter so that I can get the latest ranked record on
that day.

Hopefully you can help me out here.

Eskimo

 

[John Spencer]

Save your first query.

In a second query, join the table Collars to the saved query on Collar,
Obs_Date, MaxTime, MaxofLC

SELECT C.*
FROM COLLARS C INNER JOIN YourQuery as Q
ON C.Collar = Q.Collar
AND C.Obs_Date = Q.Obs_Date
AND C.Obs_Time = Q.MaxTime
AND C.LC = Q.MaxofLC

Hi Karl,

The simple use of two queries does not work. It provides too many records
that are redundant. Also, there are records in the query that don't match
what is available from the Collars table.

Perhaps I should start again with a recap.

Collars
--------
ID (PK, autonumber)
Collar (number) has multiples of the same collar
Obs_Date (Date, at which a Fix on the position was had)
Obs_Time (Time, at which the fix on the position was had)
LC (Number, Rank from 1-7 with 7 being highest)
Lat (Text, ##.###a ie. 63.249N the Latitude of the fix)
Lon (Text, ##.###a ie 95.567W The longitude of the fix)
ACT (Number, Activity sensor on collar that tells head/Neck Movement)

The query below gives me the maximum LC for each collar on each day. I
need
the MaxTime to give me the latest fix on that day that was also the
highest
LC. Because there is a possibility that any given collar on any given day
will report two or more records that have the same high ranking LC, but at
different times. So I need Max time to give me the latest/best LC)

I think what we need to do is first combine the collar and date as a
group.
the from each group, find the highest LC's. Then from that group with the
Highest LC's, find the latest.

Any help with suggested SQL's would be greatly appreciated.

Eskimo

Use two queries. First one pulls the max and second uses it and the
table
joined to display the rest of the information.

Why Max(c1.Obs_Time) AS MaxTime?

I have a table with 21 columns, 4 of which I use in the following query
statement that gives me a sheet with all of the collars which have the
best
signal, which is also the latest signal, on that particular day. But I
need 3
other columns that provide the Latitude and Longitude and Activity
Level for
each of the records showed up in the sql statement.

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

Out of the 21 columns, the four used in the SQL Statement above are
Collar,
Obs_Date, Obs_Time and LC. Collar is Collar Number, Obs_Date is the
date,
Obs_Time is the time and LC is an indicator of Position fix quality
(possible
values are 1 to 7 with 7 being highest).

The above statement gives me the proper records I am looking for, but I
am
having great difficulty with the adding of the columns LAT (Latitude),
LON
(Longitude) and ACT (Activity level) into the sql statement.

The trouble comes when I add those columns, it gives me more records
than
what I need. It ends up giving me all of the best LC's regardless of
what
time it reported.

I need to be able to only to retrieve the best ranked LC for each
collar for
each day, but since there can be more than one best ranked LC for each
collar
on that day, I need to filter so that I can get the latest ranked
record on
that day.

Hopefully you can help me out here.

Eskimo

 

[Guest]

Here are the two queries to pull your data --

Query named Collar_Date_Max --
SELECT Collars.Collar, Collars.Obs_Date, Max(Collars.LC) AS MaxOfLC
FROM Collars
GROUP BY Collars.Collar, Collars.Obs_Date;

SELECT Collars.Collar, Collars.Obs_Date, Collars.LC, Max(Collars.Obs_Time)
AS MaxOfObs_Time
FROM Collar_Date_Max INNER JOIN Collars ON (Collar_Date_Max.MaxOfLC =
Collars.LC) AND (Collar_Date_Max.Obs_Date = Collars.Obs_Date) AND
(Collar_Date_Max.Collar = Collars.Collar)
GROUP BY Collars.Collar, Collars.Obs_Date, Collars.LC;

Hi Karl,

The simple use of two queries does not work. It provides too many records
that are redundant. Also, there are records in the query that don't match
what is available from the Collars table.

Perhaps I should start again with a recap.

Collars
--------
ID (PK, autonumber)
Collar (number) has multiples of the same collar
Obs_Date (Date, at which a Fix on the position was had)
Obs_Time (Time, at which the fix on the position was had)
LC (Number, Rank from 1-7 with 7 being highest)
Lat (Text, ##.###a ie. 63.249N the Latitude of the fix)
Lon (Text, ##.###a ie 95.567W The longitude of the fix)
ACT (Number, Activity sensor on collar that tells head/Neck Movement)

The query below gives me the maximum LC for each collar on each day. I need
the MaxTime to give me the latest fix on that day that was also the highest
LC. Because there is a possibility that any given collar on any given day
will report two or more records that have the same high ranking LC, but at
different times. So I need Max time to give me the latest/best LC)

I think what we need to do is first combine the collar and date as a group.
the from each group, find the highest LC's. Then from that group with the
Highest LC's, find the latest.

Any help with suggested SQL's would be greatly appreciated.

Eskimo

Use two queries. First one pulls the max and second uses it and the table
joined to display the rest of the information.

Why Max(c1.Obs_Time) AS MaxTime?

I have a table with 21 columns, 4 of which I use in the following query
statement that gives me a sheet with all of the collars which have the best
signal, which is also the latest signal, on that particular day. But I need 3
other columns that provide the Latitude and Longitude and Activity Level for
each of the records showed up in the sql statement.

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

Out of the 21 columns, the four used in the SQL Statement above are Collar,
Obs_Date, Obs_Time and LC. Collar is Collar Number, Obs_Date is the date,
Obs_Time is the time and LC is an indicator of Position fix quality (possible
values are 1 to 7 with 7 being highest).

The above statement gives me the proper records I am looking for, but I am
having great difficulty with the adding of the columns LAT (Latitude), LON
(Longitude) and ACT (Activity level) into the sql statement.

The trouble comes when I add those columns, it gives me more records than
what I need. It ends up giving me all of the best LC's regardless of what
time it reported.

I need to be able to only to retrieve the best ranked LC for each collar for
each day, but since there can be more than one best ranked LC for each collar
on that day, I need to filter so that I can get the latest ranked record on
that day.

Hopefully you can help me out here.

Eskimo