469,903 Members | 2,210 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,903 developers. It's quick & easy.

Modifying date dependent query in MSAccess 2002

This is a followup to a question I posed last summer.
http://groups.google.com/groups?hl=e...eader.visi.com

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
<sm******@visi.com>


Nov 12 '05 #1
0 1146

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by vnl | last post: by
7 posts views Thread by Thomas R. Hummel | last post: by
reply views Thread by Phillip Lyles | last post: by
2 posts views Thread by msaccess | last post: by
7 posts views Thread by Jerome | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.