software

Hello,

I have a series of rows 14000 lines strong in an excel sheet.

The series contain caribou location reports from those caribou that have a
collar attached to them.

Along with other information, each row contains three important columns
related to this question. Collar Number, Date and LC (LC = Position Fix
quality or Signal Strength).

I need a formula that will find the best and latest signal strength for each
collar and for each day!. The formula should result in a 0 or 1. (no or yes).

There are about 30 different collars each reporting on certain days. The LC
values are 1 to 7 with 7 being the strongest.

If anyone can find me a solution, that would be just excellent.

Thanks in advance,

JonathanHow does 'best and latest' for each day translate into a No or Yes? That
seems a tad contradictory
quot;Eskimoquot; wrote:

gt; Hello,
gt;
gt; I have a series of rows 14000 lines strong in an excel sheet.
gt;
gt; The series contain caribou location reports from those caribou that have a
gt; collar attached to them.
gt;
gt; Along with other information, each row contains three important columns
gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; quality or Signal Strength).
gt;
gt; I need a formula that will find the best and latest signal strength for each
gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt;
gt; There are about 30 different collars each reporting on certain days. The LC
gt; values are 1 to 7 with 7 being the strongest.
gt;
gt; If anyone can find me a solution, that would be just excellent.
gt;
gt; Thanks in advance,
gt;
gt; Jonathan
gt;

Hi Duke,

I guess that it does not have to be a yes or no, that is how I used a
formula in the past to find the best/latest day.

How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
Fix's quality is not the same, so that in any one day, the fix can range from
1 - 7 with 7 being the best signal. However, there can be more than one fix
with a 7 signal. There is also a time column, which I completely forgot to
include in the original information. Forgive me.

So the formula should decipher which of the fixes is the latest-best signal.
It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
the latest best row for that particuliar collar on that particuliar day.

Thanks,

quot;Duke Careyquot; wrote:

gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; seems a tad contradictory
gt;
gt;
gt;
gt; quot;Eskimoquot; wrote:
gt;
gt; gt; Hello,
gt; gt;
gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt;
gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; collar attached to them.
gt; gt;
gt; gt; Along with other information, each row contains three important columns
gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; quality or Signal Strength).
gt; gt;
gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt;
gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt;
gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Jonathan
gt; gt;

Ok, let's recap -

You have 4 columns of data: Date/Time/Collar#/LC (any reason date amp; time
are not kept as a single value?). For any given collar you can have multiple
readings per day, and you want to find the time for the highest LC reading
(for each collar) each day, right?

This is much better handled in a database. Do you have MS Access? Do you
know how to link from Access to an Excel file? The Excel file is best laid
out with the column names in row 1, starting in col A and the data starting
in row 2. Nothing else should be on the sheet.

In Access use this query (assumes you name the linked sheet Collars, and the
columns are named Date/Time/Collar/LC)

SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
FROM Collars
WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));

quot;Eskimoquot; wrote:

gt; Hi Duke,
gt;
gt; I guess that it does not have to be a yes or no, that is how I used a
gt; formula in the past to find the best/latest day.
gt;
gt; How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
gt; Fix's quality is not the same, so that in any one day, the fix can range from
gt; 1 - 7 with 7 being the best signal. However, there can be more than one fix
gt; with a 7 signal. There is also a time column, which I completely forgot to
gt; include in the original information. Forgive me.
gt;
gt; So the formula should decipher which of the fixes is the latest-best signal.
gt; It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
gt; the latest best row for that particuliar collar on that particuliar day.
gt;
gt; Thanks,
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; gt; seems a tad contradictory
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Eskimoquot; wrote:
gt; gt;
gt; gt; gt; Hello,
gt; gt; gt;
gt; gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt; gt;
gt; gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; gt; collar attached to them.
gt; gt; gt;
gt; gt; gt; Along with other information, each row contains three important columns
gt; gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; gt; quality or Signal Strength).
gt; gt; gt;
gt; gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt; gt;
gt; gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt; gt;
gt; gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt; gt;
gt; gt; gt; Thanks in advance,
gt; gt; gt;
gt; gt; gt; Jonathan
gt; gt; gt;

Send me a sample file (remove TRUENORTH from my email address)
It is my understanding that quot;Eskimoquot; is no politically correct these days,
so I send best wishes to Inuit.
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

quot;Eskimoquot; gt; wrote in message
...
gt; Hello,
gt;
gt; I have a series of rows 14000 lines strong in an excel sheet.
gt;
gt; The series contain caribou location reports from those caribou that have a
gt; collar attached to them.
gt;
gt; Along with other information, each row contains three important columns
gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; quality or Signal Strength).
gt;
gt; I need a formula that will find the best and latest signal strength for
gt; each
gt; collar and for each day!. The formula should result in a 0 or 1. (no or
gt; yes).
gt;
gt; There are about 30 different collars each reporting on certain days. The
gt; LC
gt; values are 1 to 7 with 7 being the strongest.
gt;
gt; If anyone can find me a solution, that would be just excellent.
gt;
gt; Thanks in advance,
gt;
gt; Jonathan
gt;
Hi Duke,

I have several columns of data, but your right, there are four columns that
we need for the purpose of this newsgroup question.

I can combine the date and time to use as a single value. They come separate
into the excel file so I just keep them that way. I also need the date value
as part of the end table/excel sheet report.

To further re-cap, I have about 30 different collars, each collar has
several rows of quot;fixquot; positions so there are also several rows of the same
collar. Within each group of rows of the same collars, there are several
dates. Within each collar/date group, there are several ascending times.
Now, each row has an quot;LCquot; (acronym for Location Class). LC being the quality
of the position fix. 1 being lowest - 7 being highest.

The thing to think about is that each collar's group which report on the
same day has varying LC quality throughout the day. The best LC for that
collar on that day could come before the last report went through on that
day!.

I'll try your Access suggestion, I am very familiar with Access and can link
the excel data. can I not just import the excel sheet into a new collar named
Collars?

Thanks,

Jonathan

quot;Duke Careyquot; wrote:

gt; Ok, let's recap -
gt;
gt; You have 4 columns of data: Date/Time/Collar#/LC (any reason date amp; time
gt; are not kept as a single value?). For any given collar you can have multiple
gt; readings per day, and you want to find the time for the highest LC reading
gt; (for each collar) each day, right?
gt;
gt; This is much better handled in a database. Do you have MS Access? Do you
gt; know how to link from Access to an Excel file? The Excel file is best laid
gt; out with the column names in row 1, starting in col A and the data starting
gt; in row 2. Nothing else should be on the sheet.
gt;
gt; In Access use this query (assumes you name the linked sheet Collars, and the
gt; columns are named Date/Time/Collar/LC)
gt;
gt; SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
gt; FROM Collars
gt; WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));
gt;
gt;
gt;
gt;
gt; quot;Eskimoquot; wrote:
gt;
gt; gt; Hi Duke,
gt; gt;
gt; gt; I guess that it does not have to be a yes or no, that is how I used a
gt; gt; formula in the past to find the best/latest day.
gt; gt;
gt; gt; How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
gt; gt; Fix's quality is not the same, so that in any one day, the fix can range from
gt; gt; 1 - 7 with 7 being the best signal. However, there can be more than one fix
gt; gt; with a 7 signal. There is also a time column, which I completely forgot to
gt; gt; include in the original information. Forgive me.
gt; gt;
gt; gt; So the formula should decipher which of the fixes is the latest-best signal.
gt; gt; It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
gt; gt; the latest best row for that particuliar collar on that particuliar day.
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; gt; gt; seems a tad contradictory
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hello,
gt; gt; gt; gt;
gt; gt; gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; gt; gt; collar attached to them.
gt; gt; gt; gt;
gt; gt; gt; gt; Along with other information, each row contains three important columns
gt; gt; gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; gt; gt; quality or Signal Strength).
gt; gt; gt; gt;
gt; gt; gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt; gt; gt;
gt; gt; gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt; gt; gt;
gt; gt; gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance,
gt; gt; gt; gt;
gt; gt; gt; gt; Jonathan
gt; gt; gt; gt;

Can you just import into Access? Yes, but if the data is constantly added to
the Excel sheet then linking is better. The query of a linked sheet will
pick up any new data; otherwise you need to re-import each time you want to
run the query on new data.

The query I gave you will work just fine for the situation you have describedquot;Eskimoquot; wrote:

gt; Hi Duke,
gt;
gt; I have several columns of data, but your right, there are four columns that
gt; we need for the purpose of this newsgroup question.
gt;
gt; I can combine the date and time to use as a single value. They come separate
gt; into the excel file so I just keep them that way. I also need the date value
gt; as part of the end table/excel sheet report.
gt;
gt; To further re-cap, I have about 30 different collars, each collar has
gt; several rows of quot;fixquot; positions so there are also several rows of the same
gt; collar. Within each group of rows of the same collars, there are several
gt; dates. Within each collar/date group, there are several ascending times.
gt; Now, each row has an quot;LCquot; (acronym for Location Class). LC being the quality
gt; of the position fix. 1 being lowest - 7 being highest.
gt;
gt; The thing to think about is that each collar's group which report on the
gt; same day has varying LC quality throughout the day. The best LC for that
gt; collar on that day could come before the last report went through on that
gt; day!.
gt;
gt; I'll try your Access suggestion, I am very familiar with Access and can link
gt; the excel data. can I not just import the excel sheet into a new collar named
gt; Collars?
gt;
gt; Thanks,
gt;
gt; Jonathan
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Ok, let's recap -
gt; gt;
gt; gt; You have 4 columns of data: Date/Time/Collar#/LC (any reason date amp; time
gt; gt; are not kept as a single value?). For any given collar you can have multiple
gt; gt; readings per day, and you want to find the time for the highest LC reading
gt; gt; (for each collar) each day, right?
gt; gt;
gt; gt; This is much better handled in a database. Do you have MS Access? Do you
gt; gt; know how to link from Access to an Excel file? The Excel file is best laid
gt; gt; out with the column names in row 1, starting in col A and the data starting
gt; gt; in row 2. Nothing else should be on the sheet.
gt; gt;
gt; gt; In Access use this query (assumes you name the linked sheet Collars, and the
gt; gt; columns are named Date/Time/Collar/LC)
gt; gt;
gt; gt; SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
gt; gt; FROM Collars
gt; gt; WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Eskimoquot; wrote:
gt; gt;
gt; gt; gt; Hi Duke,
gt; gt; gt;
gt; gt; gt; I guess that it does not have to be a yes or no, that is how I used a
gt; gt; gt; formula in the past to find the best/latest day.
gt; gt; gt;
gt; gt; gt; How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
gt; gt; gt; Fix's quality is not the same, so that in any one day, the fix can range from
gt; gt; gt; 1 - 7 with 7 being the best signal. However, there can be more than one fix
gt; gt; gt; with a 7 signal. There is also a time column, which I completely forgot to
gt; gt; gt; include in the original information. Forgive me.
gt; gt; gt;
gt; gt; gt; So the formula should decipher which of the fixes is the latest-best signal.
gt; gt; gt; It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
gt; gt; gt; the latest best row for that particuliar collar on that particuliar day.
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; gt; gt; gt; seems a tad contradictory
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; gt; gt; gt; collar attached to them.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Along with other information, each row contains three important columns
gt; gt; gt; gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; gt; gt; gt; quality or Signal Strength).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; gt; gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; gt; gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks in advance,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Jonathan
gt; gt; gt; gt; gt;

Hi Duke,

I tried running the query as you described, however when I clicked on run, a
message prompt appeared saying that quot;at most one record can be returned by
this subqueryquot;

When I clicked on help, it says. a subquery of this kind cannot return more
that one record. revise the select statrment of the subquery to request only
one recordquot;.

Any thoughts?

Eskimo

quot;Duke Careyquot; wrote:

gt; Can you just import into Access? Yes, but if the data is constantly added to
gt; the Excel sheet then linking is better. The query of a linked sheet will
gt; pick up any new data; otherwise you need to re-import each time you want to
gt; run the query on new data.
gt;
gt; The query I gave you will work just fine for the situation you have described
gt;
gt;
gt; quot;Eskimoquot; wrote:
gt;
gt; gt; Hi Duke,
gt; gt;
gt; gt; I have several columns of data, but your right, there are four columns that
gt; gt; we need for the purpose of this newsgroup question.
gt; gt;
gt; gt; I can combine the date and time to use as a single value. They come separate
gt; gt; into the excel file so I just keep them that way. I also need the date value
gt; gt; as part of the end table/excel sheet report.
gt; gt;
gt; gt; To further re-cap, I have about 30 different collars, each collar has
gt; gt; several rows of quot;fixquot; positions so there are also several rows of the same
gt; gt; collar. Within each group of rows of the same collars, there are several
gt; gt; dates. Within each collar/date group, there are several ascending times.
gt; gt; Now, each row has an quot;LCquot; (acronym for Location Class). LC being the quality
gt; gt; of the position fix. 1 being lowest - 7 being highest.
gt; gt;
gt; gt; The thing to think about is that each collar's group which report on the
gt; gt; same day has varying LC quality throughout the day. The best LC for that
gt; gt; collar on that day could come before the last report went through on that
gt; gt; day!.
gt; gt;
gt; gt; I'll try your Access suggestion, I am very familiar with Access and can link
gt; gt; the excel data. can I not just import the excel sheet into a new collar named
gt; gt; Collars?
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Jonathan
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; Ok, let's recap -
gt; gt; gt;
gt; gt; gt; You have 4 columns of data: Date/Time/Collar#/LC (any reason date amp; time
gt; gt; gt; are not kept as a single value?). For any given collar you can have multiple
gt; gt; gt; readings per day, and you want to find the time for the highest LC reading
gt; gt; gt; (for each collar) each day, right?
gt; gt; gt;
gt; gt; gt; This is much better handled in a database. Do you have MS Access? Do you
gt; gt; gt; know how to link from Access to an Excel file? The Excel file is best laid
gt; gt; gt; out with the column names in row 1, starting in col A and the data starting
gt; gt; gt; in row 2. Nothing else should be on the sheet.
gt; gt; gt;
gt; gt; gt; In Access use this query (assumes you name the linked sheet Collars, and the
gt; gt; gt; columns are named Date/Time/Collar/LC)
gt; gt; gt;
gt; gt; gt; SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
gt; gt; gt; FROM Collars
gt; gt; gt; WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Hi Duke,
gt; gt; gt; gt;
gt; gt; gt; gt; I guess that it does not have to be a yes or no, that is how I used a
gt; gt; gt; gt; formula in the past to find the best/latest day.
gt; gt; gt; gt;
gt; gt; gt; gt; How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
gt; gt; gt; gt; Fix's quality is not the same, so that in any one day, the fix can range from
gt; gt; gt; gt; 1 - 7 with 7 being the best signal. However, there can be more than one fix
gt; gt; gt; gt; with a 7 signal. There is also a time column, which I completely forgot to
gt; gt; gt; gt; include in the original information. Forgive me.
gt; gt; gt; gt;
gt; gt; gt; gt; So the formula should decipher which of the fixes is the latest-best signal.
gt; gt; gt; gt; It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
gt; gt; gt; gt; the latest best row for that particuliar collar on that particuliar day.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; gt; gt; gt; gt; seems a tad contradictory
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; gt; gt; gt; gt; collar attached to them.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Along with other information, each row contains three important columns
gt; gt; gt; gt; gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; gt; gt; gt; gt; quality or Signal Strength).
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; gt; gt; gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; gt; gt; gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks in advance,
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Jonathan
gt; gt; gt; gt; gt; gt;

Hi Bernard,

Your right, the word Eskimo is not politically correct. Me being one, it
don't bother me one bit. I am not very political and not easily insulted by
such a name. :-)

I have sent you an example of the excel file by Email.

Jonathan

quot;Bernard Liengmequot; wrote:

gt; Send me a sample file (remove TRUENORTH from my email address)
gt; It is my understanding that quot;Eskimoquot; is no politically correct these days,
gt; so I send best wishes to Inuit.
gt; --
gt; Bernard V Liengme
gt; www.stfx.ca/people/bliengme
gt; remove caps from email
gt;
gt; quot;Eskimoquot; gt; wrote in message
gt; ...
gt; gt; Hello,
gt; gt;
gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt;
gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; collar attached to them.
gt; gt;
gt; gt; Along with other information, each row contains three important columns
gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; quality or Signal Strength).
gt; gt;
gt; gt; I need a formula that will find the best and latest signal strength for
gt; gt; each
gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or
gt; gt; yes).
gt; gt;
gt; gt; There are about 30 different collars each reporting on certain days. The
gt; gt; LC
gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt;
gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt;
gt; gt; Thanks in advance,
gt; gt;
gt; gt; Jonathan
gt; gt;
gt;
gt;
gt;

Sorry Eskimo -

Try this one instead

SELECT c1.Date, c1.Time, 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.Date = c2.Date
AND C1.Collar = c2.Collar
AND c1.LC = c2.MLCquot;Eskimoquot; wrote:

gt; Hi Duke,
gt;
gt; I tried running the query as you described, however when I clicked on run, a
gt; message prompt appeared saying that quot;at most one record can be returned by
gt; this subqueryquot;
gt;
gt; When I clicked on help, it says. a subquery of this kind cannot return more
gt; that one record. revise the select statrment of the subquery to request only
gt; one recordquot;.
gt;
gt; Any thoughts?
gt;
gt; Eskimo
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; Can you just import into Access? Yes, but if the data is constantly added to
gt; gt; the Excel sheet then linking is better. The query of a linked sheet will
gt; gt; pick up any new data; otherwise you need to re-import each time you want to
gt; gt; run the query on new data.
gt; gt;
gt; gt; The query I gave you will work just fine for the situation you have described
gt; gt;
gt; gt;
gt; gt; quot;Eskimoquot; wrote:
gt; gt;
gt; gt; gt; Hi Duke,
gt; gt; gt;
gt; gt; gt; I have several columns of data, but your right, there are four columns that
gt; gt; gt; we need for the purpose of this newsgroup question.
gt; gt; gt;
gt; gt; gt; I can combine the date and time to use as a single value. They come separate
gt; gt; gt; into the excel file so I just keep them that way. I also need the date value
gt; gt; gt; as part of the end table/excel sheet report.
gt; gt; gt;
gt; gt; gt; To further re-cap, I have about 30 different collars, each collar has
gt; gt; gt; several rows of quot;fixquot; positions so there are also several rows of the same
gt; gt; gt; collar. Within each group of rows of the same collars, there are several
gt; gt; gt; dates. Within each collar/date group, there are several ascending times.
gt; gt; gt; Now, each row has an quot;LCquot; (acronym for Location Class). LC being the quality
gt; gt; gt; of the position fix. 1 being lowest - 7 being highest.
gt; gt; gt;
gt; gt; gt; The thing to think about is that each collar's group which report on the
gt; gt; gt; same day has varying LC quality throughout the day. The best LC for that
gt; gt; gt; collar on that day could come before the last report went through on that
gt; gt; gt; day!.
gt; gt; gt;
gt; gt; gt; I'll try your Access suggestion, I am very familiar with Access and can link
gt; gt; gt; the excel data. can I not just import the excel sheet into a new collar named
gt; gt; gt; Collars?
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt;
gt; gt; gt; Jonathan
gt; gt; gt;
gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Ok, let's recap -
gt; gt; gt; gt;
gt; gt; gt; gt; You have 4 columns of data: Date/Time/Collar#/LC (any reason date amp; time
gt; gt; gt; gt; are not kept as a single value?). For any given collar you can have multiple
gt; gt; gt; gt; readings per day, and you want to find the time for the highest LC reading
gt; gt; gt; gt; (for each collar) each day, right?
gt; gt; gt; gt;
gt; gt; gt; gt; This is much better handled in a database. Do you have MS Access? Do you
gt; gt; gt; gt; know how to link from Access to an Excel file? The Excel file is best laid
gt; gt; gt; gt; out with the column names in row 1, starting in col A and the data starting
gt; gt; gt; gt; in row 2. Nothing else should be on the sheet.
gt; gt; gt; gt;
gt; gt; gt; gt; In Access use this query (assumes you name the linked sheet Collars, and the
gt; gt; gt; gt; columns are named Date/Time/Collar/LC)
gt; gt; gt; gt;
gt; gt; gt; gt; SELECT Collars.Date, Collars.Time, Collars.Collar, Collars.LC
gt; gt; gt; gt; FROM Collars
gt; gt; gt; gt; WHERE (((Collars.LC)=(select max(lc) from collars group by collar, date)));
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Hi Duke,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; I guess that it does not have to be a yes or no, that is how I used a
gt; gt; gt; gt; gt; formula in the past to find the best/latest day.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; How can I explain this. Each collar makes a position quot;fixquot; everyday. Each
gt; gt; gt; gt; gt; Fix's quality is not the same, so that in any one day, the fix can range from
gt; gt; gt; gt; gt; 1 - 7 with 7 being the best signal. However, there can be more than one fix
gt; gt; gt; gt; gt; with a 7 signal. There is also a time column, which I completely forgot to
gt; gt; gt; gt; gt; include in the original information. Forgive me.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; So the formula should decipher which of the fixes is the latest-best signal.
gt; gt; gt; gt; gt; It should mark a 0 for all the lower rank or earlier best, and mark a 1 for
gt; gt; gt; gt; gt; the latest best row for that particuliar collar on that particuliar day.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Duke Careyquot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; How does 'best and latest' for each day translate into a No or Yes? That
gt; gt; gt; gt; gt; gt; seems a tad contradictory
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; quot;Eskimoquot; wrote:
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Hello,
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I have a series of rows 14000 lines strong in an excel sheet.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; The series contain caribou location reports from those caribou that have a
gt; gt; gt; gt; gt; gt; gt; collar attached to them.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Along with other information, each row contains three important columns
gt; gt; gt; gt; gt; gt; gt; related to this question. Collar Number, Date and LC (LC = Position Fix
gt; gt; gt; gt; gt; gt; gt; quality or Signal Strength).
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; I need a formula that will find the best and latest signal strength for each
gt; gt; gt; gt; gt; gt; gt; collar and for each day!. The formula should result in a 0 or 1. (no or yes).
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; There are about 30 different collars each reporting on certain days. The LC
gt; gt; gt; gt; gt; gt; gt; values are 1 to 7 with 7 being the strongest.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; If anyone can find me a solution, that would be just excellent.
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Thanks in advance,
gt; gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; gt; Jonathan
gt; gt; gt; gt; gt; gt; gt;