UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Advice On The Best Way To Achieve Stock Level, Office 2013    
 
   
wornout
post Feb 13 2018, 09:32 PM
Post#1



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


I want some ideas on how to keep the stock level.
Main worksheet Called "Service Invoice" table called "Invoice" and the headers start at (B15)
the main 2 columns I will be dealing with are "Product"(B15) and "Quantity"(D15) the "Product is a data validation list cell the "Quantity" is just a normal cell
The stockSheet called "Stock" Table called "Table2" and the headers are in row 1 the columns I will be dealing with "Item"(A) "Reorder Level"(E) "In Stock"(F) "Sold"(G) "Total Sold"(H) these are not set in stone I can change them but not the Item as it is used in a few lookup equations.The "reorder level" is a predetermined amount
I am not adverse to doing macros or VBA but not allowed to have userforms


On the "Invoice" "Product" we use the drop-down list to find the Item we are selling and in the "Quantity we type in the quantity.
So what I would like to do
1) keep a telly of how much stock is left of each Item and if it drops to the reorder level have a msgbox pop up.
2) a column with how much is needed to get 5 past the reorder level
3) how many of each Item has been sold. Maybe this could go in a separate table so we could tell by date which would be the date of the invoice
I am open to different Ideas just no user forms(Which is what I am totally used to but fish out of water on the worksheet level with equations etc )
Go to the top of the page
 
wornout
post Feb 14 2018, 02:43 AM
Post#2



Posts: 1,218
Joined: 17-November 13
From: Orewa New Zealand


Ok does this make it eaiser to sugest a soultion

Service Invoice Tab table Invoice
....... B.................................C......................................D
Product............................Job................................Quantity
Caps..........................................................................5


Stock Tab Table2
....A................E......................F................G...............H
Item.......... always in stock...In Stock.........Sold.............Total sold
Caps..................10..................15

so in G it should be 5 and F should be 10 and sold should 5 so H increments G doesn't and F decreases but somewhere I have to be able to enter new stock

Go to the top of the page
 
dflak
post Feb 15 2018, 01:11 PM
Post#3


Utter Access VIP
Posts: 6,241
Joined: 22-June 04
From: North Carolina


The best way to manage inventory is to use a transaction based system. You stock it with an initial upload. Processes like manufacturing expend a certain amount. You do cycle counts and come up with a positive or negative difference and you resupply.

The amount on hand is never stored. It is always calculated as the sum of the transactions. Attached is a program that does this. The data are organized in such a way to be very amenable to pivot table analysis - you could put filters on the PT to show data where quantity less than x.

Take a look and see if it meets your needs. Instructions are included.
Attached File(s)
Attached File  Simple_Inventory.zip ( 155.22K )Number of downloads: 6
 
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th November 2018 - 04:36 AM