Date dependant query in Access 97

[Scott Morford]

I am developing a weed management database for our preserve using Access 97.
Every time an employee visits and treats a weedpatch they make a entry that
is put into the database. We're trying to determine how our treatment is
working over time. To do this we need to be able to return a table that
contains the density information of each weed patch for the first visit of
the year because subsequent visits in the same year will show a lower
density (due to previous treatment), but not accurately describe the density
year to year for our research.

My treatment table contains the fields: RecordNumber (autonumber), Weed_ID
(number), Date (date/time type in serial format), and density (text).

I am trying to create two queries.The first query is to return the earliest
record of a user specified year for each Weed_ID. The second Query is to
return the earliest record of the most recent year in which there is a
record. Some weed_id have multiple records in a given year and others have
no records in a given year.

Here is an example of the table.

RecordNumber Weed_ID Date Density
1 1 20000506 Moderate
2 1 20010607 Moderate
3 1 20010615 Low
4 2 20000615 Moderate
5 2 20000620 Low
6 3 20000510 Moderate
7 3 20010520 Moderate
8 3 20030610 Moderate
9 3 20030620 Low

For the first Query I want to return the earliest record of a specified year
for each Weed_ID. For example if I specified year 2001 the query would
return the records 2 and 7.

For the second Query I want to return the earliest record of the most recent
year for each Weed_ID. The query would return records 2,4, and 8

Is this possible using the query builder or an SQL statement in Access 97
and how might I go about doing it?

Thanks in advance for any help.
Scott Morford
Pine Butte Swamp Preserve.

 

[MGFoster]

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

Hi,

Open a query in SQL view & try these:

Query 1:

PARAMETERS [Which Year?] Date;
SELECT *
FROM Treatments As A
WHERE [Date] = (SELECT MIN([Date]) FROM Treatments
WHERE Year([Date]) = [Which Year?]
AND A.Weed_ID = Weed_ID)
ORDER BY Weed_ID, [Date]

Query 2:

SELECT *
FROM Treatments AS A
WHERE [Date] = (SELECT Min([Date]) FROM Treatments AS B
WHERE YEAR([Date]) = ( SELECT YEAR(MAX([Date]))
FROM Treatments
WHERE Weed_ID = B.Weed_ID)
AND Weed_ID = A.Weed_ID)

(Boy, that was fun...)

NOTE: Do NOT use "Date" as the name of a column, it is a VBA keyword &
can screw up queries. It is also used as a keyword in some other SQL
dialects. I've used it in my examples (properly - w/ brackets) so you
can try the SQL in your queries.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

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

iQA/AwUBP0WIhoechKqOuFEgEQLLiwCgyvV0dZsF4AG0JqjbNpjYDqDIk5sAoLKw
AXh93UbjdFqz3/zxBVh0xk5f
=zCyd
-----END PGP SIGNATURE-----

 

[Rick Collard]

Your queries will be a bit simpler if your date data was stored as a
date field rather than a number. It's also not a good idea to use a
restricted keyword as the name of a field. Assuming your table is
called Treatment and VisitedDate is a date column, the following will
work for your first query:

SELECT * From Treatment As T1
WHERE VisitedDate IN
(SELECT Min(VisitedDate)
FROM Treatment As T2
WHERE Year(VisitedDate)=2001
AND T1.Weed_ID = T2.Weed_ID
GROUP BY Weed_ID);

And the following will work for your second query:

SELECT * From Treatment As T1
WHERE VisitedDate IN
(SELECT Min(VisitedDate)
FROM Treatment As T2
WHERE Year(VisitedDate)=
(SELECT Max(Year(VisitedDate)) FROM Treatment As T3
WHERE T1.Weed_ID = T3.Weed_ID)
AND T1.Weed_ID = T2.Weed_ID
GROUP BY Weed_ID);

You should be able to alter the above to work with your date numbers
but it might just be easier to convert your table's column to a date
data type. HTH

Rick Collard
www.msc-lims.com

 

[Geoff]

Hi Scott

Your aims are:

For the first Query I want to return the earliest record
of a specified year for each Weed_ID. For example
if I specified year 2001 the query would return the
records 2 and 7.

For the second Query I want to return the earliest
record of the most recent year for each Weed_ID.
The query would return records 2,4, and 8

Yes - you can achieve both these objectives, but not
solely by writing queries.

Create two queries that sort the data so that the
records you want are the first records in each 'group'
(although you do not need to create a grouped report
to achieve your result).

For example, the first query needs to sort data by
Weed_ID (ascending), then by treatment date (ascending),
and needs to have a 'calculated' field that formats the
date into 'yyyy' with a parameter in the criterion cell of
that 'calculated' field so user can enter a year.

The second query needs to sort data by Weed_ID
(ascending), then by a 'calculated' field that formats the
date into 'yyyy' (descending), and then by Treatment Date
(ascending).

Create reports for both these queries and in the
Print Event of the detail section, enter the following code,
which will only allow the first record for each 'group' to be
printed:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

' When detail section is printed, examine/update the lngWeed_ID
' variable so as to print only the earliest-dated record.

Static lngWeed_ID As Long

If lngWeed_ID <> Me.Weed_ID Then
' The current record is the earliest date for a given
' Weed_ID, so print it and update variable:
lngWeed_ID = Me.Weed_ID
Else
' Record is for same Weed_ID but later date,
' so don't print this record:
MoveLayout = False
NextRecord = True
PrintSection = False
End If
End Sub


That will do it!
Good luck with your project.
Regards
Geoff