I'm working with fish hatchery data and need to produce a query summarizing the monthly data. Part of the summary needs to include the updated inventory for each pond, while keeping the inventory for the previous month visible (I don't want it to overwrite the previous months inventory). I have a table named Inventory that has the starting number of fish for the season and a table named Mortality that identifies the number of mortalities per month. Any guidance for how to do this is appreciated. Example: Raceway_PRAS_Unit 101 has a starting inventory of 70,083. In June 27 fish died, therefore the beginning inventory for July is 70,056. In July 29 fish died, so the beginning inventory for August is now 70,027... and so on. I'm only providing mortality data for one unit to save space.
Inventory Table
Raceway_PRAS_Unit Inventory 101 70,083 102 70,019 201 70,024 202 70,022
Mortality Table
Raceway_PRAS_Unit Mortality_Count EOM_Month 101 27 June 101 29 July 101 733 August 101 396 September