Monthly inventory update query

[Hayley]

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

[Bob Fitz]

Hi Haley

Welcome to the forum

Not sure that I understand the requirement correctly but the attached file may be of help. Post back with any questions you may have.

[Bob Fitz]

Hi Haley

Can you tell me what your local time was when you made your original post and what part of the world are you in?

[ArviLaanemets]

I think this db structure isn't best! You can instead consider something like this:
tblPonds: PondID, ...;
tblPopulationChangeTypes: PopulationChangeTypeID, PopulationChangeType, PopulationChangeDirection;
(PopulationChangeTypes are like "Adding new Fish", "Died Fish", whatever other population changes you need to be registered. PopulationChangeDirection has values 1 for types, used when new fish are added to pond, and (-1) for types used when fish are removed from pond.)
tblPondPolpulationChanges: PondPopulationChangeID, PondID, PopulationChangeType, PopulationChangeQuantity, RegistrationTime;
(RegistrationTime can be datetime, or date, or month number (when you use month number, then I advice you use for month numbers a format yyyymm) - you have to decide yourself.)
And this is all saved data what you need for this particular task. You can use a query to calculate the fish population in any pond for any time moment (Sum all population changes for this pond where registration tome is less or equal to asked time), or a query to calculate quantity changes of certain type for any period (Sum entries for this type for this pond between start and end periods), etc. You don't save those calculated data - whenever you need them, you run the query.