By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,313 Members | 2,725 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,313 IT Pros & Developers. It's quick & easy.

Modifying date dependent query in MSAccess 2002

P: n/a
This is a followup to a question I posed last summer.

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.



Nov 12 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.