Hi
In my query each record is ordered by week - each week has an individual number. I want to create a cumulative "Stock" field where-"Stock" = items made(a) - items sold(b)+last weeks "Stock"(c). (A) & (b) are manually input each week. But I can't see how to get (c) from last weeks record into this weeks so that the calculation can be set up. Can you see a way to do it?
RogerN

If you create a summary query to sum all of the items ever 'made', and to sum all of the items 'sold', the difference of these two sums will be what is 'on-hand'

(Treat an opening balance as 'made' in the first week, and treat 'shrinkage' as if it were sold)


As an aside, you might be better off having each record represent a single 'transaction' rather than a weekly summary of those transactions. Assuming you are tracking more than one kind of doohickey here, you might need tables something like:

[tblStockItems]:
ItemID (primary key)
ItemCode
ItemDescription
ItemWhateverElseYouNeed
.
.
.

[tblCodes]
CodeID (primary key)
CodeDesc

[tblTransactions]
TransID (primary key)
ItemFK
CodeFK (required)
TransDate
Quantity
UnitCost

Create one-to-many (1:M) relationships between ItemFK and ItemIF, and between CodeFK and CodeID

The codes table would contain records with descriptions like 'Opening Balance', 'Made', 'Sold', 'Shrinkage Adjustment', 'Spoiled', 'Damaged', 'Promotional Giveaway', 'Returned', etc... The idea is that every transaction would have a code to describe its' nature.

Create a form to input your transactions. Things which INCREASE stock count should have a POSITIVE quantity (such as 'Made' or 'Returned'), whereas things which DECREASE stock count should have a NEGATIVE quantity (such as 'Sold' or 'Promotional Giveaway').

Now, to determine your stock-on-hand at any time, just Sum the Quantity field. The net of the sum should be what you want. Note the 'Shrinkage Adjustment' to correct for unexplained lost units during physical inventory checks.