referencing other records (e.g., in calculated fields)

[Guest]

Is it possible to reference other records (e.g., the one preceding) in a
calculated field of a query (or by some other means)? This is easy to do in
Excel, but I have not yet figured out whether or how it is possible in
Access. (I'm working in Access 2002.)

For example, I have records of individual fish detections for 2000+ fish.
Fields are: Date, FishID, Location

I would like to create a query that includes a record of a location for
every fish on every day over a three year period. I presently can create a
query that includes a record for every day and every fish (> 2 million
records), but many records have no data for location (i.e., these are days
where a fish was not detected). Thus my desire to reference other records.
In Excel, I would write a formula in a new field that looked at the Location
field. If Location in the present record was blank, it would reference the
location from the previous day/record and fill that in for the present
record. Basically, the fish is assumed to be in the same Location until it
is detected somewhere else. And I want to have a location filled in for
every day in order to create a matrix for use in an
ordination/multi-dimensional statistics program.

I can't use Excel efficiently because of having 2,177,854 records, so I'm
hoping I can figure out a way to do this in Access. There may be another
approach that would solve my immediate problem that is different from what I
have suggested. I'd much appreciate hearing any suggestions, but I'd also
like to know an answer to my initial question as well. I have run across the
desire to reference other records in Access tables/queries many times in the
past.

 

[Guest]

As far as I can tell, what you need is a self join. Set it up as:

select T1.*
from FishLocationTbl T1 inner join FishLocationTbl T2
on T1.Location = T2.Location
order by LocationDate;

And don't use Date as the name of the field in your table. Date is a
function name in VB and as such has special significance. You may confuse
Access by using a special name as a field name.

Good Luck!

 

[Guest]

I'm sorry, I am a relative newbie when it comes to SQL statements. It would
help me if you explained a few things:
1) what the statement is doing (in English),
2) where to put/how to use this code (i.e., I've never designed a new query
using SQL code)
3) what your labels refer to -- T1/T2

Thanks,

 

[Guest]

No problem. Starting with the SQL:

select T1.*
from FishLocationTbl T1 inner join FishLocationTbl T2
on T1.Location = T2.Location
order by LocationDate;

T1.*- * is an SQL short-hand meaning 'all of the fields in the table'
T1 and T2 are simpy aliases for the table, iin this case what I called
'FishLocationTbl'. Two aliases are needed for the one table because it refers
to itself, and SQL needs you to tell it what 'copy' of the table is being
referenced.

What the statement is doing is: select from the table FishLocationTbl all of
the fields
in the table (that's the T1.*). Define the set of records from which these
records are to be selected by relating the table to itself based on the
Location field in copy 1 of the table (T1) being equal to the Location field
in copy 2 of the table (T2). Sort this set of records by the date of the
sighting.

If the table is already set up, in the Database window, select Queries and
then select 'Create Query in design view'. This will open a window with a
blank area and a grid, and a window named 'Show Table'. Select your table
from the list and click Add. Then click Add again. This will show two copies
of the table in the blank white board area. Drag the '*' from one of the
tables to the grid. Drag the LocationDate field to the next column of the
Grid, clear the check box, and in the Sort row select Ascending or
Descending. That should be all that you need.

If you want to see the SQL that has been created, right click on the
whiteboard area and select 'SQL View'. This will show you what the SQL looks
like. You can also edit the SQL directly in this view.

BTW, I would modify my SQL to read as follows:

select T1.FishID, T1. Location, T1.LocationDate
from FishLocationTbl T1 inner join FishLocationTbl T2
on T1.Location = T2.Location
group by T1.FishID, T1. Location, T1.LocationDate
order by LocationDate;

The GROUP BY will group all of the records for a fish together. The T1.* is
changed to the field names so that you can do the grouping.

I know this is wordy, and is probably not as clear as it could be. Please
ask if you don't follow.

Good Luck!

 

[Guest]

Chaim,

Thanks for the fuller explanation. I tried what you suggested, but that
doesn't accomplish what I'm trying to do. Let me try again to explain the
outcome I'm going for.

I already have a table that includes all fish detections, which includes
date of detection, fishID, Method of detection, stream, segment, distance
along stream, etc. I want to create a table or query that shows the location
of every fish not only on the dates when fish were detected, but *every day*
for a period of years.

Thus, I have made a pared-down table that looks like this (for example):
DetectionDate FishID Location
8/26/2002 1001001 MS
8/27/2002 1001001
8/28/2002 1001001
8/29/2002 1001001 T1

I want to fill in the Location field for all empty fields, *using the last
known location*. Therefore, I want the location for 8/27 and 8/28/2002 to be
MS.

Any ideas on how to do this?

 

[John Spencer (MVP)]

This could be done with a coordinated sub-query, but it would be very slow and
might very well fail. That should look something like:

SELECT T.FishID, T.DetectionDate,
(SELECT T2.Location
FROM TheFishTable as T2
WHERE T2.FishID = T.FishID and
T2.DetectionDate =
(Select Max(T1.DetectionDate) as MaxDate
From TheFishTable as T1
WHERE T1.Location is Not Null
AND T1.FishID = T.FishID
And T1.DetectionDate <= T.DetectionDate)) as TheLocation
FROM TheFishTable as T

You might be able to populate the value with with an update query using a
self-join and running it several times.

UPDATE TheFishTable as T INNER JOIN TheFishTable as T1
ON T.FishID = T1.FishID AND T.DetectionDate = T1.Detectiondate -1
SET T.Location = T1.Location
WHERE T.Location is Null and T1.Location is Not Null

Each time you ran this query it should find those records where the prior
location was filled in and fill in the next record, but you would have to run it
several times to fill in the gaps. Also, MAKE A BACKUP copy of your table
BEFORE you run this.

The query relies on your description of having a record for every DetectionDate
for each fish.