Opening/Closing Stocks (1 Viewer)

S

SiGill

Registered User.
Local time
Today, 05:51
Joined
Dec 22, 2008
Messages
72
Hi
Not sure if this has been asked before, wouldn't know how to begin looking for it to be honest as I am going to find it hard to describe.

Basically we have stocks of an item and we monitor stocks coming in and orders going out week on week. We have an opening stock, we then add on deliveries, then minus off any orders to give us the closing stock. That closing stock becomes the opening stock for the next week. Hope that makes sense.

In Excel that is pretty easy to do, but I keep getting a circular query when trying to do it in Access. How do I go about doing it?

Any help would be appreciated?

Thanks

Simon
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:51
Joined
Jan 14, 2017
Messages
18,216
The principle is the same
StockNEW = StockOLD + StockIN - StockOUT

How you implement that will depend on what you are doing
Try using an update query
 
S

SiGill

Registered User.
Local time
Today, 05:51
Joined
Dec 22, 2008
Messages
72
I have tried update queries, make table queries, etc, but each time I either get a circular reference or a destination table is the same as source table error.

I have tried to attach my database in a ZIP file but not sure it has worked because I keep getting logged out.

Query qry_DEPARTMENT SUMMARY is what does the calculation.
 
S

SiGill

Registered User.
Local time
Today, 05:51
Joined
Dec 22, 2008
Messages
72
Now attached my database.
Also a picture, from Excel, of what I am trying to achieve
 

Attachments

  • Stocks.zip
    434.1 KB · Views: 95
  • Capture.PNG
    Capture.PNG
    6.5 KB · Views: 86
Last edited:
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:51
Joined
May 7, 2009
Messages
19,237
can you give a sample data, maybe in excel with detail trainsactions.
 
S

SiGill

Registered User.
Local time
Today, 05:51
Joined
Dec 22, 2008
Messages
72
I've attached my database to the thread
 
isladogs

isladogs

MVP / VIP
Local time
Today, 13:51
Joined
Jan 14, 2017
Messages
18,216
An explanation of where to look in your database would save us a bit of time
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 13:51
Joined
Feb 19, 2013
Messages
16,607
in databases, opening stock is a calculated value, so is not stored as a value. databases are not excel - ask yourself why you need the opening stock figure - the answer is probably so you can calculate the closing stock, because that is how you do it in excel.

your stock movements should be in one table - something like

tblStkMovements
StkMovementPK
ProductFK
MoveType
MoveDate
Quantity

Movetype might be In/Out/Adjustment/OpeningBal, perhaps others

then you simply sum the quantity (assuming they are positive/negative depending on direction) to get the current stock. For opening stock, sum the quantity where the movedate<the date you want the opening stock for and for closing stock, sum the quantity where the movedate<=the date.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:51
Joined
May 7, 2009
Messages
19,237
yes, the date is very important.
you can calc the week, when it arrived, when it was delivered, when it was sold, when it was returned, etc.
also the doc / transaction number.
 
You must log in or register to reply here.

Similar threads

raziel3
Making an Opening/Closing Stock By Year Query
Replies
0
Views
355
raziel3
raziel3
R
linking forms
Replies
5
Views
343
GPGeorge
GPGeorge
R
table showing all stock qty changes from old to new value
Replies
3
Views
524
Pat Hartman
P
G
Transfer stock to location
Replies
18
Views
746
gemma-the-husky
gemma-the-husky
R
stock change for contract parts or for consumables
Replies
9
Views
464
jdraw
jdraw

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom