Hello.

I have a query that I want to filter but I can't quite get it right.

The basic table structure is as follows. I have a field SiteNum. For each site there can be many animals present. Each animal is present (on not) based on the following PresType (presence type) Confirmed, strongly suspected, possible but not likely, not suspected.

My query now lists all sites, the species present, and their PresType. In this query each site can be listed numerous times, depending on the number of species present.

..now for the question. I want to filter the query so it only displays records for sites that have animals where the PresType is 'Strongly suspected' or 'possible' but not sites where there are also species 'confirmed' or 'not susupected' .

All help is appreciated.

Thanks, DJ

Have you tried putting Strongly suspected Or possible in the criteria row for the PresType field?

Thanks for the reply pdx_man.

I had put that in the criteria row, however, this returns records for sites that have suspected animals and also has confirmed animals (even though the confirmed animal records are filtered out).

Is there a way that, if a site has any confirmed animals, all records for that site (including suspected animals) will be filtered out of the query?

Thanks again

DJ

Can you zip your DB and post it? There's something that I am missing here. You have a field, PresType, that contains one of the following values:
Confirmed, strongly suspected, possible, not suspected

You are putting Strongly suspected Or possible in the criteria row for PresType and it is returning records where the PresType is Confirmed?

I think I must be missing something here. If a site has a confirmed animal A, do you want that site to be in a recordset where animal B is suspected?

You need to do this in two parts. One part that finds the sites with confirmed sightings and the second that lists everything for those sites. The simplest method is two queries.

query1:
Select Distinct SiteID
From YourTable
Where SightingType = "Confirmed";

query2:

Select *
From YourTable Inner Join query1 ON YourTable.SiteID = query1.SiteID;

The other method is a sub-select but Jet is more efficient with the nested query method than with the sub-select.

Thanks for the help.

I now realize that the nested queries is the way to go. First eliminalte all sites that have confirmed sitings, then query the resulting recordset for sites with possilbe or suspected animals.

Thanks again,

DJ