Split into 2 tables?

[Jill]

Hi all,

I have a pond database I'm working on. We have weather data and notes
associated with a set of data collected for one pond on one day. We have
"current weather" (current weather noted for the day - ambient temperature,
wind speed, etc.) and "previous weather" which consists of notes about what
the weather has been like the past day or two. These data are contained in
one table linked to the main pond data table via foreign key... Should they
be split into 2 tables - one for current weather, one for previous weather?
What are the questions or "musts" that drive this decision? For us, the main
purpose is to be able to pull up and look over the weather notes should we
see any large changes in data, for example - to see if an overcast, rainy
day, or two or three, preceded the sunny day we are out sampling because that
can definitely have an impact...

Thanks,

Jill

 

[Jeff Boyce]

Jill

Without a bit more description, I can only offer a qualified "maybe"...

If the ONLY reason you'd consider having two different tables is because the
information about "previous" is not anything like the information about
"current", then probably yes.

If the information about weather is nearly/always the same, but you consider
anything older than (fill in a time frame) to be "previous", then simply use
a single table and make sure you include a date/time field. That way, you
could use a query to divide the weather data into previous (i.e., older than
xxxxx) and current (i.e., younger than xxxxx).

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jerry Whittle]

I would think that one table with just the weather and a date/time field
would do the trick. That way you just pull up the Top 2 records sorted by
dated descending to see the data. Heck you could even see all the previous
weather.

I think that a form based on the Pond table with a subform based on the
Weather table would be ideal.

 

[KenSheridan via AccessMonster.com]

Jill:

I would seriously doubt there would be any reason to have two tables for the
current/ previous weather, and indeed many reasons against it.

To see the previous weather records for a site you can do as Jerry suggests
and order the rows by date descending. You might even be able to use queries
which determine whether there have been significant changes since the
previous day on the basis of whatever parameters determine 'significant'.
This would mean that your weather data would need to be structured into
columns which allow the parameters to be applied. If you are simply
recording the weather as unstructured 'notes' however then this is not really
feasible.

For the purpose of simply viewing the weather data for the previous days as
you are entering new data or viewing existing sampling data for a pond/day
one approach would be have a main form in single form view with controls for
all the data, both sampling and weather, and within the form a continuous
subform based on a query which returns the previous weather data only and is
restricted to the rows for the main form's current pond and excludes the
current record, so the query for the subform might be like this:

SELECT Temperature, WindSpeed, WeatherNotes
FROM PondSamples
WHERE SampleDate < Nz(Forms!frmSamples!SampleDate,#2099-01-01#)
ORDER BY SampleDate DESC;

Where frmSamples is the name of the main parent form.

The reason for the Nz function is to return an artificially high date if the
current date in the main form is Null, as it will be when you move to a new
record, so it will then return all the previous records for the current pond.
If you navigate to an existing record in the form, then that will have a date,
so it will return the previous records before that record.

The subform should be linked to the main form on PondID by setting this as
the subform control's LinkMasterFields and LinkChildFields properties. If
you want the subform to be read-only, then set the Enabled property to False
(No) and the Locked property to True (Yes) for all bound controls in the
subform.

I think you should find the subform requeries automatically, but if not put
the following in the parent form's Current event procedure:

Me.sfcPreviousWeather.Requery

Where sfcPreviousWeather is the name of the subform control in the parent
form, i.e. the control which houses the subform, not the name of the
underlying form object, unless both should have the same name of course.

Ken Sheridan
Stafford, England