# Create new data based on date and visit

G

#### Guest

Help, I have been brain-storming on how to construct this query all day. I
finally thought I would ask some of you wonderful people. I have some survey
data where they are generating camera animal detections. The animal sets off
the camera when it goes near it and then it takes a picture of it. These
cameras are set at different stations, each station can have 1-4 human visits
to check the camera. The first visit being the set up of the camera, and the
sequential visit are to upload the data. Well each visit can have multiple
species detections etc. I am trying to construct a query that would tell me
what visit these species were detected in. I have the VisitStationId, Visit
Date and the Visit Number in one table, and in the other table I have
VisitStationId, PhotoEventDate, and species detected. So I know what Station
the animal was detected, and I have a date, but that's it. I need to know
whether UNSP1 were photographed during each visit 0=No Detection, 1 =
Detection for each visit and VisitStationId. To accomplish this I need to
relate and look at when the photographs were taken to the visit dates. For
Example. A photograph was taken on 11/MAR/2004 at Station 80401021. The
Set-up visit of "0" was on the 10/MAR/04. This new column with visit under
table two would get a 0, if it were after visit 1 date, but before visit 2
date, then it would receive a 1, if it were after visit 2 date, but before
visit 3 date, then it would receive a 2. etc. I would appreciate any
VisitStationId Visit# VisitDate
80401021 0 10/Mar/04
80401021 1 15/Mar/04
80401021 2 20/Mar/04
80401021 3 25/Mar/04
80401022 0 10/Mar/04
80401022 1 15/Mar/04
80401022 2 20/Mar/04
80401022 3 25/Mar/04
80401032 0 10/Mar/04
80401032 1 15/Mar/04
80401032 2 20/Mar/04
80401032 3 25/Mar/04
80401033 0 10/Mar/04
80401033 1 15/Mar/04
80401033 2 20/Mar/04
80401033 3 25/Mar/04
Table Two:
StationAnimalSpec PhotographDate
80401021 UNSP1 10/Mar/2004
80401021 UNSP1 11/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP1 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401022 UNSP1 10/Mar/2004

G

#### Gary Walter

Bec_FS said:
Help, I have been brain-storming on how to construct this query all day.
I
finally thought I would ask some of you wonderful people. I have some
survey
data where they are generating camera animal detections. The animal sets
off
the camera when it goes near it and then it takes a picture of it. These
cameras are set at different stations, each station can have 1-4 human
visits
to check the camera. The first visit being the set up of the camera, and
the
sequential visit are to upload the data. Well each visit can have
multiple
species detections etc. I am trying to construct a query that would tell
me
what visit these species were detected in. I have the VisitStationId,
Visit
Date and the Visit Number in one table, and in the other table I have
VisitStationId, PhotoEventDate, and species detected. So I know what
Station
the animal was detected, and I have a date, but that's it. I need to know
whether UNSP1 were photographed during each visit 0=No Detection, 1 =
Detection for each visit and VisitStationId. To accomplish this I need to
relate and look at when the photographs were taken to the visit dates.
For
Example. A photograph was taken on 11/MAR/2004 at Station 80401021. The
Set-up visit of "0" was on the 10/MAR/04. This new column with visit
under
table two would get a 0, if it were after visit 1 date, but before visit
2
date, then it would receive a 1, if it were after visit 2 date, but
before
visit 3 date, then it would receive a 2. etc. I would appreciate any
VisitStationId Visit# VisitDate
80401021 0 10/Mar/04
80401021 1 15/Mar/04
80401021 2 20/Mar/04
80401021 3 25/Mar/04
80401022 0 10/Mar/04
80401022 1 15/Mar/04
80401022 2 20/Mar/04
80401022 3 25/Mar/04
80401032 0 10/Mar/04
80401032 1 15/Mar/04
80401032 2 20/Mar/04
80401032 3 25/Mar/04
80401033 0 10/Mar/04
80401033 1 15/Mar/04
80401033 2 20/Mar/04
80401033 3 25/Mar/04
Table Two:
StationAnimalSpec PhotographDate
80401021 UNSP1 10/Mar/2004
80401021 UNSP1 11/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP1 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401022 UNSP1 10/Mar/2004

Hi Bec,

While not exactly what you want,
here might be a "divide-and-conquer"
that will get you going:

qryVisitRange

SELECT
t1.VisitStationID,
t1.[Visit#], t1.VisitDate,
(SELECT NZ(Min(t.VisitDate)-1,t1.VisitDate)
FROM TableOne As t
WHERE
t.VisitStationID=t1.VisitStationID
AND
t.VisitDate>t1.VisitDate) AS VisitEndDate
FROM TableOne AS t1;

VisitStationID Visit# VisitDate VisitEndDate
80401021 0 3/10/2004 3/14/2004
80401021 1 3/15/2004 3/19/2004
80401021 2 3/20/2004 3/24/2004
80401021 3 3/25/2004 3/25/2004
80401022 0 3/10/2004 3/14/2004
80401022 1 3/15/2004 3/19/2004
80401022 2 3/20/2004 3/24/2004
80401022 3 3/25/2004 3/25/2004
80401032 0 3/10/2004 3/14/2004
80401032 1 3/15/2004 3/19/2004
80401032 2 3/20/2004 3/24/2004
80401032 3 3/25/2004 3/25/2004
80401033 0 3/10/2004 3/14/2004
80401033 1 3/15/2004 3/19/2004
80401033 2 3/20/2004 3/24/2004
80401033 3 3/25/2004 3/25/2004

===============

qryVisitSpecPhotoDate

SELECT
qryVisitRange.VisitStationID,
qryVisitRange.[Visit#],
qryVisitRange.VisitDate,
qryVisitRange.VisitEndDate,
TableTwo.AnimalSpec,
TableTwo.PhotographDate
FROM qryVisitRange INNER JOIN TableTwo
ON qryVisitRange.VisitStationID = TableTwo.Station
WHERE
(((TableTwo.PhotographDate)
Between
[qryVisitRange].[VisitDate]
And
[qryVisitRange].[VisitEndDate]))
ORDER BY
qryVisitRange.VisitStationID,
qryVisitRange.[Visit#],
TableTwo.AnimalSpec,
TableTwo.PhotographDate;

VisitStationID
Visit# VisitDateVisitEndDate
AnimalSpecPhotographDate
80401021 0 3/10/20043/14/2004UNSP13/10/2004
80401021 0 3/10/20043/14/2004UNSP13/11/2004
80401021 2 3/20/20043/24/2004UNSP13/20/2004
80401021 2 3/20/20043/24/2004UNSP13/20/2004
80401021 2 3/20/20043/24/2004UNSP13/21/2004
80401021 2 3/20/20043/24/2004UNSP23/20/2004
80401021 2 3/20/20043/24/2004UNSP23/20/2004
80401021 2 3/20/20043/24/2004UNSP23/21/2004
80401021 2 3/20/20043/24/2004UNSP23/21/2004
80401022 0 3/10/20043/14/2004UNSP13/10/2004

========================
qryDistinctVisitSpec

SELECT DISTINCT
TableOne.VisitStationID,
TableOne.[Visit#],
TableOne.VisitDate,
TableTwo.AnimalSpec
FROM TableOne, TableTwo
ORDER BY
TableOne.VisitStationID,
TableOne.[Visit#],
TableTwo.AnimalSpec;

VisitStationID
Visit#
VisitDate AnimalSpec
80401021 0 3/10/2004 UNSP1
80401021 0 3/10/2004 UNSP2
80401021 1 3/15/2004 UNSP1
80401021 1 3/15/2004 UNSP2
80401021 2 3/20/2004 UNSP1
80401021 2 3/20/2004 UNSP2
80401021 3 3/25/2004 UNSP1
80401021 3 3/25/2004 UNSP2
80401022 0 3/10/2004 UNSP1
80401022 0 3/10/2004 UNSP2
80401022 1 3/15/2004 UNSP1
80401022 1 3/15/2004 UNSP2
80401022 2 3/20/2004 UNSP1
80401022 2 3/20/2004 UNSP2
80401022 3 3/25/2004 UNSP1
80401022 3 3/25/2004 UNSP2
80401032 0 3/10/2004 UNSP1
80401032 0 3/10/2004 UNSP2
80401032 1 3/15/2004 UNSP1
80401032 1 3/15/2004 UNSP2
80401032 2 3/20/2004 UNSP1
80401032 2 3/20/2004 UNSP2
80401032 3 3/25/2004 UNSP1
80401032 3 3/25/2004 UNSP2
80401033 0 3/10/2004 UNSP1
80401033 0 3/10/2004 UNSP2
80401033 1 3/15/2004 UNSP1
80401033 1 3/15/2004 UNSP2
80401033 2 3/20/2004 UNSP1
80401033 2 3/20/2004 UNSP2
80401033 3 3/25/2004 UNSP1
80401033 3 3/25/2004 UNSP2

==============
qryPhotoCounts

SELECT
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#],
qryDistinctVisitSpec.VisitDate,
qryDistinctVisitSpec.AnimalSpec,
Count(qryVisitSpecPhotoDate.PhotographDate)
AS PhotoCount
FROM qryDistinctVisitSpec
LEFT JOIN
qryVisitSpecPhotoDate
ON
(qryDistinctVisitSpec.VisitStationID
= qryVisitSpecPhotoDate.VisitStationID)
AND
(qryDistinctVisitSpec.[Visit#]
= qryVisitSpecPhotoDate.[Visit#])
AND
(qryDistinctVisitSpec.AnimalSpec
= qryVisitSpecPhotoDate.AnimalSpec)
GROUP BY
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#],
qryDistinctVisitSpec.VisitDate,
qryDistinctVisitSpec.AnimalSpec
ORDER BY
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#];

VisitStationID
Visit# VisitDate AnimalSpec
PhotoCount
80401021 0 3/10/2004 UNSP1 2
80401021 0 3/10/2004 UNSP2 0
80401021 1 3/15/2004 UNSP1 0
80401021 1 3/15/2004 UNSP2 0
80401021 2 3/20/2004 UNSP1 3
80401021 2 3/20/2004 UNSP2 4
80401021 3 3/25/2004 UNSP1 0
80401021 3 3/25/2004 UNSP2 0
80401022 0 3/10/2004 UNSP1 1
80401022 0 3/10/2004 UNSP2 0
80401022 1 3/15/2004 UNSP1 0
80401022 1 3/15/2004 UNSP2 0
80401022 2 3/20/2004 UNSP1 0
80401022 2 3/20/2004 UNSP2 0
80401022 3 3/25/2004 UNSP1 0
80401022 3 3/25/2004 UNSP2 0
80401032 0 3/10/2004 UNSP1 0
80401032 0 3/10/2004 UNSP2 0
80401032 1 3/15/2004 UNSP1 0
80401032 1 3/15/2004 UNSP2 0
80401032 2 3/20/2004 UNSP1 0
80401032 2 3/20/2004 UNSP2 0
80401032 3 3/25/2004 UNSP1 0
80401032 3 3/25/2004 UNSP2 0
80401033 0 3/10/2004 UNSP1 0
80401033 0 3/10/2004 UNSP2 0
80401033 1 3/15/2004 UNSP1 0
80401033 1 3/15/2004 UNSP2 0
80401033 2 3/20/2004 UNSP1 0
80401033 2 3/20/2004 UNSP2 0
80401033 3 3/25/2004 UNSP1 0
80401033 3 3/25/2004 UNSP2 0

G

#### Guest

Gary, Thank you so much for responding so quickly, and I am happy to say that
this is almost exactly what I needed. Wish I had your experience and ability
to solve problems so quickly!!

Gary Walter said:
Bec_FS said:
Help, I have been brain-storming on how to construct this query all day.
I
finally thought I would ask some of you wonderful people. I have some
survey
data where they are generating camera animal detections. The animal sets
off
the camera when it goes near it and then it takes a picture of it. These
cameras are set at different stations, each station can have 1-4 human
visits
to check the camera. The first visit being the set up of the camera, and
the
sequential visit are to upload the data. Well each visit can have
multiple
species detections etc. I am trying to construct a query that would tell
me
what visit these species were detected in. I have the VisitStationId,
Visit
Date and the Visit Number in one table, and in the other table I have
VisitStationId, PhotoEventDate, and species detected. So I know what
Station
the animal was detected, and I have a date, but that's it. I need to know
whether UNSP1 were photographed during each visit 0=No Detection, 1 =
Detection for each visit and VisitStationId. To accomplish this I need to
relate and look at when the photographs were taken to the visit dates.
For
Example. A photograph was taken on 11/MAR/2004 at Station 80401021. The
Set-up visit of "0" was on the 10/MAR/04. This new column with visit
under
table two would get a 0, if it were after visit 1 date, but before visit
2
date, then it would receive a 1, if it were after visit 2 date, but
before
visit 3 date, then it would receive a 2. etc. I would appreciate any
VisitStationId Visit# VisitDate
80401021 0 10/Mar/04
80401021 1 15/Mar/04
80401021 2 20/Mar/04
80401021 3 25/Mar/04
80401022 0 10/Mar/04
80401022 1 15/Mar/04
80401022 2 20/Mar/04
80401022 3 25/Mar/04
80401032 0 10/Mar/04
80401032 1 15/Mar/04
80401032 2 20/Mar/04
80401032 3 25/Mar/04
80401033 0 10/Mar/04
80401033 1 15/Mar/04
80401033 2 20/Mar/04
80401033 3 25/Mar/04
Table Two:
StationAnimalSpec PhotographDate
80401021 UNSP1 10/Mar/2004
80401021 UNSP1 11/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP1 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP2 20/Mar/2004
80401021 UNSP1 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401021 UNSP2 21/Mar/2004
80401022 UNSP1 10/Mar/2004

Hi Bec,

While not exactly what you want,
here might be a "divide-and-conquer"
that will get you going:

qryVisitRange

SELECT
t1.VisitStationID,
t1.[Visit#], t1.VisitDate,
(SELECT NZ(Min(t.VisitDate)-1,t1.VisitDate)
FROM TableOne As t
WHERE
t.VisitStationID=t1.VisitStationID
AND
t.VisitDate>t1.VisitDate) AS VisitEndDate
FROM TableOne AS t1;

VisitStationID Visit# VisitDate VisitEndDate
80401021 0 3/10/2004 3/14/2004
80401021 1 3/15/2004 3/19/2004
80401021 2 3/20/2004 3/24/2004
80401021 3 3/25/2004 3/25/2004
80401022 0 3/10/2004 3/14/2004
80401022 1 3/15/2004 3/19/2004
80401022 2 3/20/2004 3/24/2004
80401022 3 3/25/2004 3/25/2004
80401032 0 3/10/2004 3/14/2004
80401032 1 3/15/2004 3/19/2004
80401032 2 3/20/2004 3/24/2004
80401032 3 3/25/2004 3/25/2004
80401033 0 3/10/2004 3/14/2004
80401033 1 3/15/2004 3/19/2004
80401033 2 3/20/2004 3/24/2004
80401033 3 3/25/2004 3/25/2004

===============

qryVisitSpecPhotoDate

SELECT
qryVisitRange.VisitStationID,
qryVisitRange.[Visit#],
qryVisitRange.VisitDate,
qryVisitRange.VisitEndDate,
TableTwo.AnimalSpec,
TableTwo.PhotographDate
FROM qryVisitRange INNER JOIN TableTwo
ON qryVisitRange.VisitStationID = TableTwo.Station
WHERE
(((TableTwo.PhotographDate)
Between
[qryVisitRange].[VisitDate]
And
[qryVisitRange].[VisitEndDate]))
ORDER BY
qryVisitRange.VisitStationID,
qryVisitRange.[Visit#],
TableTwo.AnimalSpec,
TableTwo.PhotographDate;

VisitStationID
Visit# VisitDateVisitEndDate
AnimalSpecPhotographDate
80401021 0 3/10/20043/14/2004UNSP13/10/2004
80401021 0 3/10/20043/14/2004UNSP13/11/2004
80401021 2 3/20/20043/24/2004UNSP13/20/2004
80401021 2 3/20/20043/24/2004UNSP13/20/2004
80401021 2 3/20/20043/24/2004UNSP13/21/2004
80401021 2 3/20/20043/24/2004UNSP23/20/2004
80401021 2 3/20/20043/24/2004UNSP23/20/2004
80401021 2 3/20/20043/24/2004UNSP23/21/2004
80401021 2 3/20/20043/24/2004UNSP23/21/2004
80401022 0 3/10/20043/14/2004UNSP13/10/2004

========================
qryDistinctVisitSpec

SELECT DISTINCT
TableOne.VisitStationID,
TableOne.[Visit#],
TableOne.VisitDate,
TableTwo.AnimalSpec
FROM TableOne, TableTwo
ORDER BY
TableOne.VisitStationID,
TableOne.[Visit#],
TableTwo.AnimalSpec;

VisitStationID
Visit#
VisitDate AnimalSpec
80401021 0 3/10/2004 UNSP1
80401021 0 3/10/2004 UNSP2
80401021 1 3/15/2004 UNSP1
80401021 1 3/15/2004 UNSP2
80401021 2 3/20/2004 UNSP1
80401021 2 3/20/2004 UNSP2
80401021 3 3/25/2004 UNSP1
80401021 3 3/25/2004 UNSP2
80401022 0 3/10/2004 UNSP1
80401022 0 3/10/2004 UNSP2
80401022 1 3/15/2004 UNSP1
80401022 1 3/15/2004 UNSP2
80401022 2 3/20/2004 UNSP1
80401022 2 3/20/2004 UNSP2
80401022 3 3/25/2004 UNSP1
80401022 3 3/25/2004 UNSP2
80401032 0 3/10/2004 UNSP1
80401032 0 3/10/2004 UNSP2
80401032 1 3/15/2004 UNSP1
80401032 1 3/15/2004 UNSP2
80401032 2 3/20/2004 UNSP1
80401032 2 3/20/2004 UNSP2
80401032 3 3/25/2004 UNSP1
80401032 3 3/25/2004 UNSP2
80401033 0 3/10/2004 UNSP1
80401033 0 3/10/2004 UNSP2
80401033 1 3/15/2004 UNSP1
80401033 1 3/15/2004 UNSP2
80401033 2 3/20/2004 UNSP1
80401033 2 3/20/2004 UNSP2
80401033 3 3/25/2004 UNSP1
80401033 3 3/25/2004 UNSP2

==============
qryPhotoCounts

SELECT
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#],
qryDistinctVisitSpec.VisitDate,
qryDistinctVisitSpec.AnimalSpec,
Count(qryVisitSpecPhotoDate.PhotographDate)
AS PhotoCount
FROM qryDistinctVisitSpec
LEFT JOIN
qryVisitSpecPhotoDate
ON
(qryDistinctVisitSpec.VisitStationID
= qryVisitSpecPhotoDate.VisitStationID)
AND
(qryDistinctVisitSpec.[Visit#]
= qryVisitSpecPhotoDate.[Visit#])
AND
(qryDistinctVisitSpec.AnimalSpec
= qryVisitSpecPhotoDate.AnimalSpec)
GROUP BY
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#],
qryDistinctVisitSpec.VisitDate,
qryDistinctVisitSpec.AnimalSpec
ORDER BY
qryDistinctVisitSpec.VisitStationID,
qryDistinctVisitSpec.[Visit#];

VisitStationID
Visit# VisitDate AnimalSpec
PhotoCount
80401021 0 3/10/2004 UNSP1 2
80401021 0 3/10/2004 UNSP2 0
80401021 1 3/15/2004 UNSP1 0
80401021 1 3/15/2004 UNSP2 0
80401021 2 3/20/2004 UNSP1 3
80401021 2 3/20/2004 UNSP2 4
80401021 3 3/25/2004 UNSP1 0
80401021 3 3/25/2004 UNSP2 0
80401022 0 3/10/2004 UNSP1 1
80401022 0 3/10/2004 UNSP2 0
80401022 1 3/15/2004 UNSP1 0
80401022 1 3/15/2004 UNSP2 0
80401022 2 3/20/2004 UNSP1 0
80401022 2 3/20/2004 UNSP2 0
80401022 3 3/25/2004 UNSP1 0
80401022 3 3/25/2004 UNSP2 0
80401032 0 3/10/2004 UNSP1 0
80401032 0 3/10/2004 UNSP2 0