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
advice!!!!!
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
80401032 TADO 15/Mar/2004
80401032 TADO 15/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 21/Mar/2004
80401033 TADO 21/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
advice!!!!!
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
80401032 TADO 15/Mar/2004
80401032 TADO 15/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 21/Mar/2004
80401033 TADO 21/Mar/2004
Click to expand...

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
80401032 1 3/15/20043/19/2004TADO3/15/2004
80401032 1 3/15/20043/19/2004TADO3/15/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/21/2004
80401033 2 3/20/20043/24/2004TADO3/21/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 TADO
80401021 0 3/10/2004 UNSP1
80401021 0 3/10/2004 UNSP2
80401021 1 3/15/2004 TADO
80401021 1 3/15/2004 UNSP1
80401021 1 3/15/2004 UNSP2
80401021 2 3/20/2004 TADO
80401021 2 3/20/2004 UNSP1
80401021 2 3/20/2004 UNSP2
80401021 3 3/25/2004 TADO
80401021 3 3/25/2004 UNSP1
80401021 3 3/25/2004 UNSP2
80401022 0 3/10/2004 TADO
80401022 0 3/10/2004 UNSP1
80401022 0 3/10/2004 UNSP2
80401022 1 3/15/2004 TADO
80401022 1 3/15/2004 UNSP1
80401022 1 3/15/2004 UNSP2
80401022 2 3/20/2004 TADO
80401022 2 3/20/2004 UNSP1
80401022 2 3/20/2004 UNSP2
80401022 3 3/25/2004 TADO
80401022 3 3/25/2004 UNSP1
80401022 3 3/25/2004 UNSP2
80401032 0 3/10/2004 TADO
80401032 0 3/10/2004 UNSP1
80401032 0 3/10/2004 UNSP2
80401032 1 3/15/2004 TADO
80401032 1 3/15/2004 UNSP1
80401032 1 3/15/2004 UNSP2
80401032 2 3/20/2004 TADO
80401032 2 3/20/2004 UNSP1
80401032 2 3/20/2004 UNSP2
80401032 3 3/25/2004 TADO
80401032 3 3/25/2004 UNSP1
80401032 3 3/25/2004 UNSP2
80401033 0 3/10/2004 TADO
80401033 0 3/10/2004 UNSP1
80401033 0 3/10/2004 UNSP2
80401033 1 3/15/2004 TADO
80401033 1 3/15/2004 UNSP1
80401033 1 3/15/2004 UNSP2
80401033 2 3/20/2004 TADO
80401033 2 3/20/2004 UNSP1
80401033 2 3/20/2004 UNSP2
80401033 3 3/25/2004 TADO
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 TADO 0
80401021 0 3/10/2004 UNSP1 2
80401021 0 3/10/2004 UNSP2 0
80401021 1 3/15/2004 TADO 0
80401021 1 3/15/2004 UNSP1 0
80401021 1 3/15/2004 UNSP2 0
80401021 2 3/20/2004 TADO 0
80401021 2 3/20/2004 UNSP1 3
80401021 2 3/20/2004 UNSP2 4
80401021 3 3/25/2004 TADO 0
80401021 3 3/25/2004 UNSP1 0
80401021 3 3/25/2004 UNSP2 0
80401022 0 3/10/2004 TADO 0
80401022 0 3/10/2004 UNSP1 1
80401022 0 3/10/2004 UNSP2 0
80401022 1 3/15/2004 TADO 0
80401022 1 3/15/2004 UNSP1 0
80401022 1 3/15/2004 UNSP2 0
80401022 2 3/20/2004 TADO 0
80401022 2 3/20/2004 UNSP1 0
80401022 2 3/20/2004 UNSP2 0
80401022 3 3/25/2004 TADO 0
80401022 3 3/25/2004 UNSP1 0
80401022 3 3/25/2004 UNSP2 0
80401032 0 3/10/2004 TADO 0
80401032 0 3/10/2004 UNSP1 0
80401032 0 3/10/2004 UNSP2 0
80401032 1 3/15/2004 TADO 6
80401032 1 3/15/2004 UNSP1 0
80401032 1 3/15/2004 UNSP2 0
80401032 2 3/20/2004 TADO 0
80401032 2 3/20/2004 UNSP1 0
80401032 2 3/20/2004 UNSP2 0
80401032 3 3/25/2004 TADO 0
80401032 3 3/25/2004 UNSP1 0
80401032 3 3/25/2004 UNSP2 0
80401033 0 3/10/2004 TADO 0
80401033 0 3/10/2004 UNSP1 0
80401033 0 3/10/2004 UNSP2 0
80401033 1 3/15/2004 TADO 0
80401033 1 3/15/2004 UNSP1 0
80401033 1 3/15/2004 UNSP2 0
80401033 2 3/20/2004 TADO 6
80401033 2 3/20/2004 UNSP1 0
80401033 2 3/20/2004 UNSP2 0
80401033 3 3/25/2004 TADO 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
advice!!!!!
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
80401032 TADO 15/Mar/2004
80401032 TADO 15/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401032 TADO 16/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 20/Mar/2004
80401033 TADO 21/Mar/2004
80401033 TADO 21/Mar/2004
Click to expand...

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
80401032 1 3/15/20043/19/2004TADO3/15/2004
80401032 1 3/15/20043/19/2004TADO3/15/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401032 1 3/15/20043/19/2004TADO3/16/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/20/2004
80401033 2 3/20/20043/24/2004TADO3/21/2004
80401033 2 3/20/20043/24/2004TADO3/21/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 TADO
80401021 0 3/10/2004 UNSP1
80401021 0 3/10/2004 UNSP2
80401021 1 3/15/2004 TADO
80401021 1 3/15/2004 UNSP1
80401021 1 3/15/2004 UNSP2
80401021 2 3/20/2004 TADO
80401021 2 3/20/2004 UNSP1
80401021 2 3/20/2004 UNSP2
80401021 3 3/25/2004 TADO
80401021 3 3/25/2004 UNSP1
80401021 3 3/25/2004 UNSP2
80401022 0 3/10/2004 TADO
80401022 0 3/10/2004 UNSP1
80401022 0 3/10/2004 UNSP2
80401022 1 3/15/2004 TADO
80401022 1 3/15/2004 UNSP1
80401022 1 3/15/2004 UNSP2
80401022 2 3/20/2004 TADO
80401022 2 3/20/2004 UNSP1
80401022 2 3/20/2004 UNSP2
80401022 3 3/25/2004 TADO
80401022 3 3/25/2004 UNSP1
80401022 3 3/25/2004 UNSP2
80401032 0 3/10/2004 TADO
80401032 0 3/10/2004 UNSP1
80401032 0 3/10/2004 UNSP2
80401032 1 3/15/2004 TADO
80401032 1 3/15/2004 UNSP1
80401032 1 3/15/2004 UNSP2
80401032 2 3/20/2004 TADO
80401032 2 3/20/2004 UNSP1
80401032 2 3/20/2004 UNSP2
80401032 3 3/25/2004 TADO
80401032 3 3/25/2004 UNSP1
80401032 3 3/25/2004 UNSP2
80401033 0 3/10/2004 TADO
80401033 0 3/10/2004 UNSP1
80401033 0 3/10/2004 UNSP2
80401033 1 3/15/2004 TADO
80401033 1 3/15/2004 UNSP1
80401033 1 3/15/2004 UNSP2
80401033 2 3/20/2004 TADO
80401033 2 3/20/2004 UNSP1
80401033 2 3/20/2004 UNSP2
80401033 3 3/25/2004 TADO
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 TADO 0
80401021 0 3/10/2004 UNSP1 2
80401021 0 3/10/2004 UNSP2 0
80401021 1 3/15/2004 TADO 0
80401021 1 3/15/2004 UNSP1 0
80401021 1 3/15/2004 UNSP2 0
80401021 2 3/20/2004 TADO 0
80401021 2 3/20/2004 UNSP1 3
80401021 2 3/20/2004 UNSP2 4
80401021 3 3/25/2004 TADO 0
80401021 3 3/25/2004 UNSP1 0
80401021 3 3/25/2004 UNSP2 0
80401022 0 3/10/2004 TADO 0
80401022 0 3/10/2004 UNSP1 1
80401022 0 3/10/2004 UNSP2 0
80401022 1 3/15/2004 TADO 0
80401022 1 3/15/2004 UNSP1 0
80401022 1 3/15/2004 UNSP2 0
80401022 2 3/20/2004 TADO 0
80401022 2 3/20/2004 UNSP1 0
80401022 2 3/20/2004 UNSP2 0
80401022 3 3/25/2004 TADO 0
80401022 3 3/25/2004 UNSP1 0
80401022 3 3/25/2004 UNSP2 0
80401032 0 3/10/2004 TADO 0
80401032 0 3/10/2004 UNSP1 0
80401032 0 3/10/2004 UNSP2 0
80401032 1 3/15/2004 TADO 6
80401032 1 3/15/2004 UNSP1 0
80401032 1 3/15/2004 UNSP2 0
80401032 2 3/20/2004 TADO 0
80401032 2 3/20/2004 UNSP1 0
80401032 2 3/20/2004 UNSP2 0
80401032 3 3/25/2004 TADO 0
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate End Date Using Start Date and No. of Days excluding specific days. 0
Lookup value and return column offset value 5
Number of hrs worked in a week, based on dates in a different column 1
Combine 2 Charts 2007 Excel 1
Summing values to today - tidier version 5
Summing values to today 3
Group individual dates into seperate months 3
Crosstab Query based on Ordinal Values 6

Top