Date calculations for planting

[leetx]

Hello - I'm not sure if this is possible and my guess is that I need to know more about SQL than I do.. but here it goes:

I have a gardening database and for each plant I want to calculate the planting date based on a certain number of weeks. For example, Plant A should be planted 4-6 weeks before the last frost in my area and Plant B should be planted 2-3 weeks AFTER the last frost date.

What makes sense to me is that I have a table with each plant and a field for Firstweek and Lastweek. I would also have a table with years and LastFrostDate. With those two tables I would run a query to calculate the planting date, correct?

What is the calculation that I would do for that? What would the code look like?

Or maybe I am thinking about this incorrectly and there is a better way...

Any guidance would be appreciated to get me in the right direction.

Thanks - Lisa

 

[MarkK]

I might create a WeeksFromLastFrost datapoint and items that come before the frost are negative, items that come after are positive. Then maybe add a field for the variability like WeeksFromLastFrostVariance, so for PlantA ....

WeeksFromLastFrost = -5
WeeksFromLastFrostVariance = 1

For PlantB

WeeksFromLastFrost = 2.5
WeeksFromLastFrostVariance = 0.5

Then preferred planting date is easily and consistently calculable as ...

LastFrost + Plant.WeeksFromLastFrost

... and then +/- the variance as the case may be.
Just another option for you ...

 

[PNGBill]

Also, in sql you have dateAdd - here an extract from my Access 2000 help

DateAdd Function Example
The following example shows how you can create a calculated control by using the DateAdd function to display the date by which a particular order must be shipped — in this case, thirty days after the OrderDate. Suppose you have a form based on an Orders table, with a field called OrderDate. You can create another text box on the form to display the shipping date by setting the ControlSource property of this text box as in the following example.
= DateAdd("d", 30, [OrderDate])

 

[leetx]

Thank you so much for the quick response. I'm still pretty new to Access - so it will take me some time to digest these suggestions and put them into practice... Hopefully I won't have to many problems...

- L