Filtering a query

[DJ44]

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

 

[pdx_man]

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

 

[DJ44]

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

 

[pdx_man]

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?

 

[DJ44]

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