general advice needed on db structure

[mike_980]

Hi everyone, firstly thankyou to everyone who has helped me so far i'd be lost without this forum!



I have recently created a database that calculates the amount of manure our farm is allowed to spread due to regulations. The limits are based on a number of things but one is the crop which the field currently has in. At present I have a form which navigates the recordset of our fields (tbl_destination) and the current crop is stored in there, and modified every year. But a long story short we now want it to be able to look back 2 or 3 years to see what crop it has had in, and be able to generate all reports as though they had been printed at the respective time as a problem is that at the moment, i can jump back a few years to pull information back, but they do the calculations presuming that they had the current crop at the time if that makes sense?

I will attach a screenshot of my table relationships if it will help but sadly the database is about 4mb compacted so too large to upload - the cluster of tables related to my issue are the ones at the bottom of the screen around tbl_destination

What do you guys suggest is the best way to adapt my database to allow reports to display correctly? New crops get assigned to the database roughly 1st July every year, so a temporary fix is to print all reports before new crops are assigned and keep the records on paper, but that defeats the point slightly of using this system...

thanks in advance!

[John_G]

I think you can solve your problem with one new table. Modify your table Tbl_Destination so that it describes a field, but not what is in it, because that changes from year to year.

Create a new table (let's call it Tbl_Planting), which might contains fields like this:

Planting_ID
Crop_Year <-- this is the information you need to track from year to year
Destination_ID
Crop_ID
.... + other data relevant to that planting

This table now tells you what crop was planted in which field in which year.


Link Tbl_Crop, Tbl_spreading_fert and Tbl_spreading to the new table Tbl_Planting, because they relate to a particular planting, not a particular Destination.

Post back if you need more clarification, but this should get you started.

John

[mike_980]

Thanks John, that sounds like it might get me going yes I understand what you mean I will have a go tonight to see how I come on and post back if I run into any trouble. The only issue I can see with this is that different crops are planted at different times of year but all are generally harvested between July and October. I will need some kind of validation as my perceved "2013" crop maybe different to another users (is a 2013 crop sown or harvested in 2013?) but at a guess a July-July cycle could work so maybe the year field could be 2012-2013,2013-2014 etc etc? Sorry I'm thinking out loud here!

Thanks for your help though and any further input or potential issues are welcome!

Mike

[John_G]

Hi -

If you used a date field (e.g. Planting_Date) instead of Crop_year that would work just as well, or better. You can always extract the year part of a date using the Year(Date) function, e.g. in a query, so that exact dates don't matter.

John

[mike_980]

That's some food for thought, I will do some tests tomorrow morning when i'm back at work and report back with how I get on. Thanks again!
Mike

[Dal Jeanis]

Yeah, looks like modifying the tbl_Crop and tbl_Destination, or adding another table in that area, will solve the need. I'd tend to revisit those two and see if the CropID needs moved off of tbl_Destination into a junction table with dates on it.

With regard to growing seasons, I would also strongly recommend backing up into as abstract a design as possible. If a field might have a six-crop rotation in one year (WalMart ordered a million tons of radishes) how would you support it?