Stock\inventory Control

[tweety]

hi and thanks alcapps for the idea, abit of background the database itself: i purchase goods from 3 suppliers, the quantity is stored in the products table. for example if i purchased 10 items on the 2 feb 2013 and only sold 8 to customers i have 2 remaining, hence the next purchase will 8 and not 10. so i have done what alcapps was mentioned above by adding another query.





(qtyonhand+qtyPurchased)-qtySold

This gives me




is there a reason why gum bubblem and gomets do not have a number in the stock? is it because i did not purchase any from the supplier??
i have a report that reads\is based on the products table, this shows the amount in stock (at hand)

Ill try (and error) the next bit (i understand its not advised) that is to use an update query to write the new results from the query above into the products table. This is so the next purchase order and quantity at hand can be added and then subtracted from the amount sold.

I dont understand why and how "There is a chance the UPDATE action could be accidentally repeated or forgotten" what if i run this query once the stock report is ran, it will only update once?

many thanks to all

[June7]

Nothing is foolproof. If you rely on user to push 'Update Stock' button, what if they forget to push at all or get distracted and push it twice? The queries I suggested are to calculate the stock level at any time. Not advised to be used for updating table.

[alcapps]

I agree with june7 calculations is the way to go unless you are doing a history table at the end of a business day routine.

[tweety]

sorry i should have mentioned this before hand, its done at the end of the end, so 99% the end user will only click it once. iv looked online on how to update the stock row to the product table, any ideas or links your know of? iv tried to put in the update to field [product].[quantity] but access is giving me an error message.

thanks

[June7]

What is 'end of the end'?

I still think if you really want to do this then the stock adjustment should be done at the time each transaction is entered, not with an aggregate query.

[tweety]

sorry i meant at the end of the day, i was thinking to finish off the update query, then if it doesnt work well try the way you mentioned, to do it the way you are saying once a sale or a purchased is recorded,



where or how do i update the stock? will the user by clicking a button or after a field is updated (Quantity) the value is updated (plus if purchased and minus if sold) in the products table?

thanks

[tweety]

Has anyone got any links on how to store query results to a table, I have checked on Google but didn't find anythink, this is this site i found http://office.microsoft.com/en-gb/ac...76527.aspx#BM4

after trying this, access gives this error message


Thanks

[June7]

In some event (button click or textbox AfterUpdate or form BeforeUpdate) maybe like:

CurrentDb.Execute "UPDATE Products SET Quantity = Quantity - " & Me.QtySold & " WHERE ProductID = " & Me.ProductID

or

CurrentDb.Execute "UPDATE Products SET Quantity = Quantity + " & Me.QtyPurchased & " WHERE ProductID = " & Me.ProductID

However, before you commit to this approach please read http://www.allenbrowne.com/AppInventory.html

[tweety]

I really dont know what to say? THANKS! with the above commands the stock is updating. at first i put the code into "after update" but this did not work as the default quantity is set to 1, so i moved it over to "on Exit" seems to work fine after testing both sales and purchase orders.

Currently there is validation in place so the product table show quantity as minus. Saying this before joined the forum i looked alot on the net for stock control and i did come accros allen brownes link, i would have throught there would be many samples of stock but anyway

Thanks again to all

[tweety]

is there a way to validate the stock e.g if the stock is =0 then a message is displayed while a product is sold

the way this system is setup is, items are sold durning the day and in the evening i sit on the computer and record sales made to find out the profit, so there is never a change i sell a product which is out of stock

[June7]

Did you mean 'chance' instead of 'change'?

Yes, can do a DLookup for the current balance on hand.

How can you sell something that is not on the shelf? If you don't enter sale at time of purchase how can you know the actual quantity available for the next buyer?

[tweety]

yes sorry
can you guide me in using the dlookup please
the way this works is i load my car up with current stock, but more for the suplliers if needs be, then go around to the shops and sell. at the ned of the day i come back home, enter the purchase orders and then go through the sales bit (of the system) sellecting each customer and putting in their items. a notes is made while items are purchased and sold

[June7]

Access Help (or Google search) has guidelines on domain aggregate functions (DLookup, DSum, DAvg, etc).

Can be used in expression in query or textbox or VBA.

Post your attempt for analysis if have issue.

[tweety]

Sorry I forgot to attach the system

[June7]

Don't see any attempt at using DLookup in VBA. Have you tried it somewhere else? Review Access Help on use these functions. Attempt code. Post code for analysis.