Modifying date dependent query in MSAccess 2002

[Scott Morford]

This is a followup to a question I posed last summer.
http://groups.google.com/[email protected]

I'm developing a weed management database to track our weed management
practices in the eventual hope of using some statistical models on the data
to measure outcome\success. The database contains principally two tables. One
table contains static information such as an weed patch identifier
<weed_id>, species <species>, Location, etc. The second table contains data
that we collect about each patch from year to year such as treatment date
<tdate>, density <cover_class>, treatment <treatment>, etc. The two tables
are linked via the <weed_id> value.

Initally, with the help of the people on this group, I developed a query
that would report the first density record of each weed_id for a given year
specified by the user. A weed patch could be visited multiple times
in a given year, or not visited at all. Since we treat the weeds patches with
herbicide, the only density value of use in tracking density over time
is the first visit, because each sequential visit would yield densities
influenced by the recent herbicide application.

The query ended up looking like this.

PARAMETERS [Which Year?] Long;
SELECT T1.Weed_id, [Static Infestation Data].Species, T1.[Cover Class]
FROM [Static Infestation Data] INNER JOIN Treatment AS T1 ON [Static
Infestation Data].Weed_id = T1.Weed_id
WHERE (((T1.Date) In (SELECT Min(Date)
FROM Treatment As T2
WHERE Year(Date)= [Which Year?]
AND T1.Weed_ID = T2.Weed_ID
GROUP BY Weed_ID)));

The query works great, however, the data does not fit well into our
statistical analysis package because each table produced does not contain
an entry for every weed_id (remember, some patches are not visited at all in
a given year).

This query needs to be modified so that a value is returned for every
weed_id that is present in the static table. What we would like to do,
instead of inserting a null value, is to insert the density value from the
most recent previous year (again, reporting earliest value in that year).

For example: I run a query for 2001. There are no records in the treatment
table matching year 2001 for weed_id 5. Instead, the earliest record of 2000
is reported for weed_id 5 is reported (or perhaps the earliest record for
year 1998 if it has not been visited since then).

I'm not quite sure where to start with this one because I'm no SQL expert.
Any help you would have would be greatly appreciated.

Thanks

Scott
<[email protected]>

 

[Gary Floam]

Scott,

You need a hierarchy of 3 queries.

Query 1 would have WeedID and Years where there are any reports of weed
density at all.

Query 2 would have the first weed report for each weed-patch/year
combination.

Query 3 would be base on query 2 and would have the weed id and the latest
date for that weed-id.

Query 4 would contain the data you are looking for.

Hope this helps,

Gary

This is a followup to a question I posed last summer.
http://groups.google.com/[email protected]

I'm developing a weed management database to track our weed management
practices in the eventual hope of using some statistical models on the data
to measure outcome\success. The database contains principally two tables. One
table contains static information such as an weed patch identifier
<weed_id>, species <species>, Location, etc. The second table contains data
that we collect about each patch from year to year such as treatment date
<tdate>, density <cover_class>, treatment <treatment>, etc. The two tables
are linked via the <weed_id> value.

Initally, with the help of the people on this group, I developed a query
that would report the first density record of each weed_id for a given year
specified by the user. A weed patch could be visited multiple times
in a given year, or not visited at all. Since we treat the weeds patches with
herbicide, the only density value of use in tracking density over time
is the first visit, because each sequential visit would yield densities
influenced by the recent herbicide application.

The query ended up looking like this.

PARAMETERS [Which Year?] Long;
SELECT T1.Weed_id, [Static Infestation Data].Species, T1.[Cover Class]
FROM [Static Infestation Data] INNER JOIN Treatment AS T1 ON [Static
Infestation Data].Weed_id = T1.Weed_id
WHERE (((T1.Date) In (SELECT Min(Date)
FROM Treatment As T2
WHERE Year(Date)= [Which Year?]
AND T1.Weed_ID = T2.Weed_ID
GROUP BY Weed_ID)));

The query works great, however, the data does not fit well into our
statistical analysis package because each table produced does not contain
an entry for every weed_id (remember, some patches are not visited at all in
a given year).

This query needs to be modified so that a value is returned for every
weed_id that is present in the static table. What we would like to do,
instead of inserting a null value, is to insert the density value from the
most recent previous year (again, reporting earliest value in that year).

For example: I run a query for 2001. There are no records in the treatment
table matching year 2001 for weed_id 5. Instead, the earliest record of 2000
is reported for weed_id 5 is reported (or perhaps the earliest record for
year 1998 if it has not been visited since then).

I'm not quite sure where to start with this one because I'm no SQL expert.
Any help you would have would be greatly appreciated.

Thanks

Scott
<[email protected]>