Hi Duke,

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

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 record".

Any thoughts?

Eskimo

"Duke Carey" wrote:

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 described


"Eskimo" wrote:

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 "fix" 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 "LC" (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

"Duke Carey" wrote:

Ok, let's recap -

You have 4 columns of data: Date/Time/Collar#/LC (any reason date & 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)));




"Eskimo" wrote:

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 "fix" 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,

"Duke Carey" wrote:

How does 'best and latest' for each day translate into a No or Yes? That
seems a tad contradictory



"Eskimo" wrote:

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,

Jonathan