Date Query using the NOT operator (Access 97)

[Scott Morford]

I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.

Access 97 seems to handle the NOT operator in a non standard fashion, that or
my syntax is really messed up.

Any help is greatly appreciated.

Scott Morford
Pine Butte Swamp Preserve

[Pieter Linden]

Scott Morford <sm******@visi. com> wrote in message news:<3f******* *************** @newsreader.vis i.com>...

I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.

I think NOT is a red herring... you want an outer join.

SELECT ST.Weed_ID, ST.Species
FROM ST LEFT JOIN TT ON ST.WeedID=TT.We edID
WHERE TT.Treatment_da te NOT BETWEEN Date() AND
DateAdd("yyyy", Date(),-1);

or something like that... but you *definitely* need an outer join.
The part I'm not sure of is the exact syntax of the Between clause.

HTH,
Village Idiot

[Marc]

Scott Morford <sm******@visi. com> wrote in message news:<3f******* *************** @newsreader.vis i.com>...

I am developing a weed management database for the preserve I work on. One
of the queries I'm working on will allow the user to run a query and see which weed
patches have NOT been treated in the current calendar year.

This query involves two tables. The first contains the static information
about the weed patches. The second contains Treatment information. The
tables look something like this

Static Table fields:
Weed_ID (integer)
Species (text)

Treatment Table Fields:
Treatment number (autonumber)
Weed_ID (integer)
Treatment_date (date/time)
Treatment_type (text)

The tables are joined by Weed_ID

The query needs to parse the treatment table and return [static table].weed_id
and [static table].species where there is NO treatment record(s) with
treatment_date values in the current calendar year.

Access 97 seems to handle the NOT operator in a non standard fashion, that or
my syntax is really messed up.

Any help is greatly appreciated.

Scott Morford
Pine Butte Swamp Preserve

Scott,]

Her are 2 solutions:

one using a left outer join and a group by clause (a:
SELECT [Static Table].weed_id, [Static Table].species
FROM [Static Table] LEFT JOIN Treatment ON [Static Table].weed_id =
Treatment.weed_ id
WHERE (((Year([treatment_date]))<>Year(Date() ) Or
(Year([treatment_date])) Is Null))
GROUP BY [Static Table].weed_id, [Static Table].species;
and a more elegant one with a subquery:
SELECT [Static Table].weed_id, [Static Table].species
FROM [Static Table]
WHERE ((([Static Table].weed_id) Not In (select [weed_id] from
treatment where year([treatment_date]) = year(date())))) ;
Both are made in the query-editor of Access in QBE mode. In tha last
one the SQL subquery expression 'Not in ...' must be written manually
in the criteria box.

Marc

[Pieter Linden]

I would stay away from a NOT IN subquery if possible. a LEFT JOIN is
*much* faster... <borrowing Kent Brockman's 2 cents...>