Linking three tables for a farming database

[HanHan]

Hello!

I’m building a database to run my farm better. I have three tables:

• ‘Catalogue’, where crop names, photos etc are kept
• ‘Planting log’ where I create a record each time I plant a crop (how much, where, planting dates, expected harvest window, and expected harvest per week in kg)
• ‘Harvest log’ where I record what crop I harvest, kg, and cost to customer

What I can’t seem to work out is how to link these three together. I would like a simple way of seeing Expected total harvest kg (from ‘Planting log’) minus kg harvested (from ‘Harvesting log’).

I often harvest the same crop weekly over months, so the total needs to look for the crop name and subtract each harvest from the Expected total harvest kg.

Basically - a quick way to see what’s in the field, taking into account what I expected harvest to be when planting and taking away from that kg number with each harvest recorded in the ‘Harvesting log’.

[JonathanBowen]

Hi @HanHan - here’s how I would approach this:

Your catalogue table:

Your planting log table:

The “expected harvest” field is a formula multiplying expected harvest per week by harvesting window.

On your harvesting log table, you can only harvest something that has been planted, so the link I would have is between the planting and the harvesting tables:

Back in the planting log table, you can see the linked harvest records and can roll-up the harvested total:

You can also calculate the “still to harvest” value by taking the harvested to date value from the expected harvest.

If you want to see the total by crop type back on the catalogue table, you can rollup the harvested amount (from the planting table now), back to the catalogue table. This would be useful if you had one crop in multiple locations:

JB

[HanHan]

THANK YOU so much Jonathan, that’s exactly what I needed to do!

Hanna