how to get previous days closing stock (1 Viewer)

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.

I'd vote for transaction tables and a summation query for which a given date would be referenced in the WHERE clause for the sums. Split the feed arrival and consumption as transactions. (Also, see later, other transactions).

You start with FeedXAct (XAct meaning transactions)
XDate: Date of transaction
XAmt: Amount of transaction
XType: Type of transaction as a coded value

Then XActType table gives you
XType: Type of transaction as a coded value (this COULD be as simple as an autonumber)
XTypeName: What the transaction represents in English
XSign: Either +1 or -1 depending on whether transaction adds or subtracts from the total

Now do a query joining the two (on the XType code) that includes all the other fields and contains this computed field: XSign * XAmt As XQty

Now when you want the total for a given date you run a summation query of that query (yes, a query of a query) including SUM(XQty) where XDate < target date (for the opening quantity on-hand) or XDate <= target date (for the closing quantity on-hand) for that date.

If you want to archive this stuff, you can have a transaction type that represents an archival date. Do things in this order:

1. Compute the closing amount for that date (and remember the amount and the date)
2. (Optional if you need this) Export or copy all entries <= that date to another table or to a spreadsheet or wherever you put your archives.
3. Delete all entries <= that date
4. Create an entry with the archival date and the closing amount. The XSign for that transaction would be +1.

What that does is it lets you clean out old transaction history and reset the base date with the closing total for that date. So the next transaction starts with the proper total for the opening date (one day later.)

Doing it this way allows you to also keep track of entries like "bad feed detected & destroyed" (XSign = -1).

I might suggest that your "eggs" tracking could ALSO be split that way if you have "Eggs laid" and "Eggs taken" and "Eggs Hatched" transactions, plus "Eggs lost to egg-sucking dogs" (or whatever your actual hazard might be. Raccoons? Foxes?) Or "Omelets made". (Hmm... I see my sense of whimsy is striking...)

This could also work for the chickens with transactions like "Chickens hatched" "Chickens Died (Natural causes)" "Chickens Euthanized" "Chickens Fried (Yum)" etc.

What that would give you is a pot-load of report possibilities that could be used to develop trend information by using the Graph add-in to plot transaction types against date. It would make for GREAT summary reports that would pop out at you instantly.

Note also that ONE transaction type table could serve ALL of the things you track, since you can have as small as a two-byte (WORD) code and have 65k different named transactions. Or let it be four bytes (LONG) and you have 4 billion different names. Though I'd be surprised if you had as many as 100 unique names.

One last thought. I see that the batch number sometimes is referenced. There is no reason why you couldn't include a batch number in the transaction where it would be relevant, so that you can produce both summaries across batches (by ignoring the batch number) or batch summaries (group by batch number). Whether this could be one big happy transaction table or whether you would want to keep transaction types separate as a conceptual thing is going to be your call.

thanks for taking the time for that long answer. i think it will take me longer to just go through it then you spent on typing it