alright. i hope I can explain it correctly.
Relevant tables
Farm
Batch
Sdate
Shed
Farm table holds
farm_id - primary key
sdate_id (references sdate table that has sdate being a date of entry. this is in a table as it is referenced by other tables as well)
batch_id (references batch_id primary key from batch table)
prod (eggs produced)
culled (chickens culled)
Mortality (chickens dying)
egg_stock (eggs currently in stock up to that moment)
fd_arv (feed arrival)
fd_con (feed consumed)
water (water consumed)
notes (notes)
Batch holds
batch_id - primary key
shed_id (references the shed_id primary key in shed table)
batch_name (name of batch)
batch_date (date of batch)
birds_stock (no. of chickens in stock during batch creation)
obsolete (checkmark to mark whether obsolete)
Shed holds
shed_id - primary key
shed_name (name of shed)
to keep this really simple I need to be able to have an opening and closing stock for feed (Based on the logic of this I can do the same for the other stocks as well)
So one day I have a opening stock of 100. feed_arv is 10 and feed_con is 5 that would mean next day's closing stock is 105.
What would be the best way to get the opening stock so previous day's closing stock can be retrieved.
do i have to setup transactions table which then calculates and outputs based on query or should i have a vba that retrieved previous day's stock on creation of a new record. please tell me best way forward.
Relevant tables
Farm
Batch
Sdate
Shed
Farm table holds
farm_id - primary key
sdate_id (references sdate table that has sdate being a date of entry. this is in a table as it is referenced by other tables as well)
batch_id (references batch_id primary key from batch table)
prod (eggs produced)
culled (chickens culled)
Mortality (chickens dying)
egg_stock (eggs currently in stock up to that moment)
fd_arv (feed arrival)
fd_con (feed consumed)
water (water consumed)
notes (notes)
Batch holds
batch_id - primary key
shed_id (references the shed_id primary key in shed table)
batch_name (name of batch)
batch_date (date of batch)
birds_stock (no. of chickens in stock during batch creation)
obsolete (checkmark to mark whether obsolete)
Shed holds
shed_id - primary key
shed_name (name of shed)
to keep this really simple I need to be able to have an opening and closing stock for feed (Based on the logic of this I can do the same for the other stocks as well)
So one day I have a opening stock of 100. feed_arv is 10 and feed_con is 5 that would mean next day's closing stock is 105.
What would be the best way to get the opening stock so previous day's closing stock can be retrieved.
do i have to setup transactions table which then calculates and outputs based on query or should i have a vba that retrieved previous day's stock on creation of a new record. please tell me best way forward.