Multiple Selection Criteria

[Guest]

Thank you so much for taking the time to read this, and even more so if you
respond!!!
I am trying to get Site Fidelity for a group of species. I figured this
would be a simple query, but it is turning out not to be. I have a table
with a bunch of animal species, pit-tag, and data captured. Sometime and
pit-tagged animal was captured more than once per year, and sometimes
captured multiple years. I was trying to query where pit-tag was in 2003 AND
2004, but that does not seem to work. I tried using an OR between dates,
but then it gives me the pittags that were in 2003, but not 2004 or vica
versa. I want the pit-tag if it was captured in 2003 AND 2004, and so on
2004 AND 2005, 2005 and 2006. Can you not use the AND in criteria for one
field? Hope this makes sense!

 

[Gary Walter]

Thank you so much for taking the time to read this, and even more so if you
respond!!!
I am trying to get Site Fidelity for a group of species. I figured this
would be a simple query, but it is turning out not to be. I have a table
with a bunch of animal species, pit-tag, and data captured. Sometime and
pit-tagged animal was captured more than once per year, and sometimes
captured multiple years. I was trying to query where pit-tag was in 2003 AND
2004, but that does not seem to work. I tried using an OR between dates,
but then it gives me the pittags that were in 2003, but not 2004 or vica
versa. I want the pit-tag if it was captured in 2003 AND 2004, and so on
2004 AND 2005, 2005 and 2006. Can you not use the AND in criteria for one
field? Hope this makes sense!

When Access is looking at a record in your query
and deciding whether to return it or not, its capture
date field cannot be 2003 and 2004 at the same time.
It is either one year or the other.

Does that make sense?

One strategy would be to switch to a totals query,
decide what gets included in using

WHERE Year([capturefield]) IN (2003, 2004)

i.e., only include in a group if 2003 OR 2004...

then, after get your groups, weed out groups that
don't have both 2003 AND 2004

HAVING Min(Year([capturefield]))<>Max(Year([capturefield])

i.e., if min was equal to max in the group,
you'd know did not have both 2003 AND 2004...

 

[Guest]

You need criteria like this --
(Pit-tag AND Year) OR (Pit-tag AND Year)

In the query design view put your Pit-tag and year both in the same row.
Then drop down for the next Pit-tag and year combination to search for.

 

[Gary Walter]

Thank you so much for taking the time to read this, and even more so if you
respond!!!
I am trying to get Site Fidelity for a group of species. I figured this
would be a simple query, but it is turning out not to be. I have a table
with a bunch of animal species, pit-tag, and data captured. Sometime and
pit-tagged animal was captured more than once per year, and sometimes
captured multiple years. I was trying to query where pit-tag was in

2003

AND
2004, but that does not seem to work. I tried using an OR between dates,
but then it gives me the pittags that were in 2003, but not 2004 or vica
versa. I want the pit-tag if it was captured in 2003 AND 2004, and so on
2004 AND 2005, 2005 and 2006. Can you not use the AND in criteria for one
field? Hope this makes sense!

When Access is looking at a record in your query
and deciding whether to return it or not, its capture
date field cannot be 2003 and 2004 at the same time.
It is either one year or the other.

Does that make sense?

One strategy would be to switch to a totals query,
decide what gets included in using

WHERE Year([capturefield]) IN (2003, 2004)

i.e., only include in a group if 2003 OR 2004...

then, after get your groups, weed out groups that
don't have both 2003 AND 2004

HAVING Min(Year([capturefield]))<>Max(Year([capturefield])

i.e., if min was equal to max in the group,
you'd know did not have both 2003 AND 2004...

Another way of looking at your data would involve
using 3 instances of your table...

(you probably only need 2 instances, so this may be
more an exercise in what is possible, but learning
what you need as well..if that makes sense)

In a new query add your table 3 times.

Right-mouse click on one table,
choose "Properties"
and change "Alias" to "HereAndNow"

Likewise, change Alias of second table to "Previous"
and third table to "Next"

Drag-and-drop field(s) from "HereAndNow" to
"Previous" that will "match them up" (you know your
data).

Likewise, set join(s) from "Previous" to "Next."

Double-click on top of table of "HereAndNow"
to select all fields, then drag-and-drop multiselection
down to a "Field" row in grid.

I'm not entirely clear how some field ends up being a "year"
so continue as above assuming you had a "capturefield"
that contained the date of capture, and "year" was calculated
by "Year([capturefield])"

If so, double-click on [capturefield] in each table
to add them to end of grid.

Field: capturefield
Table: HereAndNow
Sort:
Show: <checked>
Criteria:
Or:

Field: capturefield
Table: Previous
Sort:
Show: <checked>
Criteria:
Or:

Field: capturefield
Table: Next
Sort:
Show: <checked>
Criteria:
Or:

(maybe you don't have to do this, but) change
"Field" rows of these 3 columns to get "year"
and give each an alias to keep track of them .

Field: HNYear: Year([capturefield])
Table: HereAndNow
Sort:
Show: <checked>
Criteria:
Or:


Field: PrevYear: Year([capturefield])
Table: Previous
Sort:
Show: <checked>
Criteria:
Or:


Field: NextYear: Year([capturefield])
Table: Next
Sort:
Show: <checked>
Criteria:
Or:

And now -- why we did all this ---
set Criteria for "Previous" and "Next" years
to define their time relationship to "HereAndNow."

Field: PrevYear: Year([capturefield])
Table: Previous
Sort:
Show: <checked>
Criteria: Year(HereAndNow.capturefield) - 1
Or:

Field: NextYear: Year([capturefield])
Table: Next
Sort:
Show: <checked>
Criteria: Year(HereAndNow.capturefield) + 1
Or:

Save the query and see what we are getting.

For a specific record in "HereAndNow,"
if a capture happened for that species
in the previous and next year, it will be
returned (possibly more than once because
of multicaptures in a year).

As I said earlier this is more than you wanted
(just an exercise in "past/present/future").

My guess is you would probably want to delete the
"Previous" table from the query and set
Criteria for HNYear to year you are interested in.

Plus, because you can have more than one capture
for a species in one year, you will probably need to

--lose the primary key (delete column for "HereAndNow"
ID or whatever you named it)
--lose the "full date" capturefield column (if it exists)
--set Property of query for Distinct Values

to return only one record for each species.

Well...that might be another approach to the problem...

 

[Guest]

Not sure that I totally understand this method, but I did take a shot at it
and it did not work, probably because I did not do it right. Here is what I
did... I chose the field PitTagNumber from tblCaptures, In the query ERD is
my tblSurveyInfo with the field SurveyDate which is linked to tblCaptures
with the field PitTagNumber. I selected PitTagNumber for the field, from
table tblCaptures, and put this statement in the criteria.
([PitTagNumber] And Year([SurveyDate])=2003) Or ([PitTagNumber] And
Year([SurveyDate])=2004)
Is this what you meant?

 

[Guest]

Gary, thank you so much for your response. I printed your ideas out and I am
going to try and read through them again to try and make sense out of your
idea, then hopefully I can try it out. I did come to one conclusion, but it
is not the best. I ended up making a cross-tab of the data, putting PitTag
as the column, Year as the header, and then counted my UniqueCaptureId. I
then was going to query that for null versus not null in each year column.
Probably not the best way to do it, I would still like to try your idea and
Karl's, because we come across this query a lot in my field of work!

Thank you so much for taking the time to read this, and even more so if you
respond!!!
I am trying to get Site Fidelity for a group of species. I figured this
would be a simple query, but it is turning out not to be. I have a table
with a bunch of animal species, pit-tag, and data captured. Sometime and
pit-tagged animal was captured more than once per year, and sometimes
captured multiple years. I was trying to query where pit-tag was in

2003

AND
2004, but that does not seem to work. I tried using an OR between dates,
but then it gives me the pittags that were in 2003, but not 2004 or vica
versa. I want the pit-tag if it was captured in 2003 AND 2004, and so on
2004 AND 2005, 2005 and 2006. Can you not use the AND in criteria for one
field? Hope this makes sense!

When Access is looking at a record in your query
and deciding whether to return it or not, its capture
date field cannot be 2003 and 2004 at the same time.
It is either one year or the other.

Does that make sense?

One strategy would be to switch to a totals query,
decide what gets included in using

WHERE Year([capturefield]) IN (2003, 2004)

i.e., only include in a group if 2003 OR 2004...

then, after get your groups, weed out groups that
don't have both 2003 AND 2004

HAVING Min(Year([capturefield]))<>Max(Year([capturefield])

i.e., if min was equal to max in the group,
you'd know did not have both 2003 AND 2004...

Another way of looking at your data would involve
using 3 instances of your table...

(you probably only need 2 instances, so this may be
more an exercise in what is possible, but learning
what you need as well..if that makes sense)

In a new query add your table 3 times.

Right-mouse click on one table,
choose "Properties"
and change "Alias" to "HereAndNow"

Likewise, change Alias of second table to "Previous"
and third table to "Next"

Drag-and-drop field(s) from "HereAndNow" to
"Previous" that will "match them up" (you know your
data).

Likewise, set join(s) from "Previous" to "Next."

Double-click on top of table of "HereAndNow"
to select all fields, then drag-and-drop multiselection
down to a "Field" row in grid.

I'm not entirely clear how some field ends up being a "year"
so continue as above assuming you had a "capturefield"
that contained the date of capture, and "year" was calculated
by "Year([capturefield])"

If so, double-click on [capturefield] in each table
to add them to end of grid.

Field: capturefield
Table: HereAndNow
Sort:
Show: <checked>
Criteria:
Or:

Field: capturefield
Table: Previous
Sort:
Show: <checked>
Criteria:
Or:

Field: capturefield
Table: Next
Sort:
Show: <checked>
Criteria:
Or:

(maybe you don't have to do this, but) change
"Field" rows of these 3 columns to get "year"
and give each an alias to keep track of them .

Field: HNYear: Year([capturefield])
Table: HereAndNow
Sort:
Show: <checked>
Criteria:
Or:


Field: PrevYear: Year([capturefield])
Table: Previous
Sort:
Show: <checked>
Criteria:
Or:


Field: NextYear: Year([capturefield])
Table: Next
Sort:
Show: <checked>
Criteria:
Or:

And now -- why we did all this ---
set Criteria for "Previous" and "Next" years
to define their time relationship to "HereAndNow."

Field: PrevYear: Year([capturefield])
Table: Previous
Sort:
Show: <checked>
Criteria: Year(HereAndNow.capturefield) - 1
Or:

Field: NextYear: Year([capturefield])
Table: Next
Sort:
Show: <checked>
Criteria: Year(HereAndNow.capturefield) + 1
Or:

Save the query and see what we are getting.

For a specific record in "HereAndNow,"
if a capture happened for that species
in the previous and next year, it will be
returned (possibly more than once because
of multicaptures in a year).

As I said earlier this is more than you wanted
(just an exercise in "past/present/future").

My guess is you would probably want to delete the
"Previous" table from the query and set
Criteria for HNYear to year you are interested in.

Plus, because you can have more than one capture
for a species in one year, you will probably need to

--lose the primary key (delete column for "HereAndNow"
ID or whatever you named it)
--lose the "full date" capturefield column (if it exists)
--set Property of query for Distinct Values

to return only one record for each species.

Well...that might be another approach to the problem...

 

[Guest]

I re-read what you posted and I think these three queries will pull the data
you want.

Bec_FS_Multi_Capture_Year --
SELECT tblCaptures.PitTagNumber, "N/A" AS [First Year], "N/A" AS [Second
Year], Year([SurveyDate]) AS [Capture year], Count(tblCaptures.PitTagNumber)
AS [Caught per year]
FROM tblCaptures
GROUP BY tblCaptures.PitTagNumber, "N/A", "N/A", Year([SurveyDate])
HAVING (((Count(tblCaptures.PitTagNumber))>1));

Bec_FS_ConsecutiveYears --
SELECT tblCaptures.PitTagNumber, Year(tblCaptures.SurveyDate) AS [First
Year], Year(tblCaptures_1.SurveyDate) AS [Second Year], "N/A" AS [Capture
year], "N/A" AS [Caught per year]
FROM tblCaptures INNER JOIN tblCaptures AS tblCaptures_1 ON
tblCaptures.PitTagNumber = tblCaptures_1.PitTagNumber
WHERE
(((Year([tblCaptures_1].[SurveyDate]))=Year([tblCaptures].[SurveyDate])+1));

This is a UNION query to bring it all together --
SELECT Bec_FS_ConsecutiveYears.PitTagNumber, Bec_FS_ConsecutiveYears.[First
Year], Bec_FS_ConsecutiveYears.[Second Year],
Bec_FS_ConsecutiveYears.[Capture year], Bec_FS_ConsecutiveYears.[Caught per
year]
FROM Bec_FS_ConsecutiveYears
UNION SELECT Bec_FS_Multi_Capture_Year.PitTagNumber,
Bec_FS_Multi_Capture_Year.[First Year], Bec_FS_Multi_Capture_Year.[Second
Year], Bec_FS_Multi_Capture_Year.[Capture year],
Bec_FS_Multi_Capture_Year.[Caught per year]
FROM Bec_FS_Multi_Capture_Year;

Not sure that I totally understand this method, but I did take a shot at it
and it did not work, probably because I did not do it right. Here is what I
did... I chose the field PitTagNumber from tblCaptures, In the query ERD is
my tblSurveyInfo with the field SurveyDate which is linked to tblCaptures
with the field PitTagNumber. I selected PitTagNumber for the field, from
table tblCaptures, and put this statement in the criteria.
([PitTagNumber] And Year([SurveyDate])=2003) Or ([PitTagNumber] And
Year([SurveyDate])=2004)
Is this what you meant?

You need criteria like this --
(Pit-tag AND Year) OR (Pit-tag AND Year)

In the query design view put your Pit-tag and year both in the same row.
Then drop down for the next Pit-tag and year combination to search for.