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

 

[Bob Quintal]

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

---------------------
SELECT Weed_ID, species from [static table]
where weed_ID not in
(SELECT weed_ID from [treatment table]
WHERE year(treatement_date)=year(now)
)
;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

You don't show your SQL code so I can't comment on it. Here is my SQL
code that gets what you want:

PARAMETERS [What Year?] Integer;
SELECT S.Weed_ID, S.Species
FROM Static As S LEFT JOIN Treatment As T ON S.Weed_ID=T.Weed_ID
WHERE Year(T.Treatment_date) = [What Year?]
AND T.Weed_ID IS NULL

I used the Access built-in functions Year() to get the calendar year
that the user enters in the [What Year?] parameter prompt.

To get records in the Left table (Static) that do not have related
records in the Right table (Treatment) use the WHERE clause <right table
name>.<related column> IS NULL syntax. If there isn't a related record
in the Right table the related column (Weed_ID) will be NULL.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP2edr4echKqOuFEgEQIIigCffrt/XAKhcBg/p8u2bVgMxgfua+cAn21B
GS4BOhD7ylolA/+qiMVb2UtH
=CTC4
-----END PGP SIGNATURE-----

 

[Allen Browne]

A subquery is often the easiest way to ask about what it NOT there.

This kind of thing:

SELECT [Static Table].[Weed_ID], [Static Table].[Species]
FROM [Static Table] WHERE NOT EXISTS
( SELECT [Treatment number] FROM [Treatment Table]
WHERE [Treatment Table].[Weed_ID] = [Static Table].[Weed_ID]
AND [Treatment Table].[Treatment date] Between
DateSerial(Year(Date()), 1,1) And DateSerial(Year(Date(), 12, 31) )

 

[Pieter Linden]

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.WeedID
WHERE TT.Treatment_date 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]

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