Stock Update
Apr 11, 2006
I have a customer table with all the usual customer details (customer ID, name, address etc...), a transaction table (Transaction ID, Order Number, Customer ID, Title ID, Date, Status (order received, payment taken, ready for dispatch etc...)), and i have a Title table (Title ID, Artist ID, Name, Record Label, Quantity, etc...)
what would be the easiest way to update the title table everytime a new transaction was added to tblTransaction so that the quantity field in tblTitle would go down by one for the right title?
View Replies
ADVERTISEMENT
Jun 19, 2007
Hi,
I have a client who needs to build kits out items in his stock. So far I have a form where he can link the kit id to part id's. On the same form I have a text box which he can type in the kit id, then there is a list box which shows the parts linked to this kit, the list box shows the partid, part no and how many are in stock. I then have a text box which he can type in how many kits he wants to build. What I'm having trouble with is how to create an update query which will loop through the list box and adjust the stock to match whatever amount of kits he wants to build. So if he chooses to build one kit then one item will be added to the kit id and one will be taken away from all the parts in the list box linked to the kit id. I've searched this forum and can't quite find anything that will do this.
Thanks
View 4 Replies
View Related
Nov 21, 2007
basically, im making a stock control database
i've made an update query but its not exactly what i wanted
is there a way to make the criteria equal a particular form field because i dont want the user to type in the stock ref in a parameter query.
also, how do i make it so no orders can be made on stock which has stock levels of 0 and have a message saying it is out of stock
at the moment, i put >0 in the criteria underneath no in stock field in the update query
i hope i made it clear because im not sure how to explain it
thanks for any help
the jayman
View 4 Replies
View Related
Nov 21, 2013
I have a database that deals with rented movies. I want a means in Access to enable the stock values of the movie to be updated whenever there is an order for a certain film and whenever its returned. I am not sure if an update query or a macro is needed for this to work and how each would work.
View 3 Replies
View Related
Nov 15, 2012
I'm trying to refine my Invoice system, although i'd love to update my Stock table with the 'Invoiced amount'.
I select the StockID (or in this case 'UniformID') using a lookup. My thoughts are how best to update the stock quantity after this invoice is issued.
Here's one way i've been thinking... Could i use the AfterUpdate event of the UniformID to run an update query? But would that be wise?
Here's the screen dump of what i'm developing...
View 1 Replies
View Related
Nov 14, 2014
I have an order form with an order details sub form. Both are bound to their respective tables and function correctly, with the OrderID being inserted automatically into the order details table.
However I am using VBA to update and check stock and have it working when attached to a separate form called process orders. Which event would be the best place to place this code. I do not really want to have the process orders form. Would it be on the master form or the sub form. I thought of placing it in the after insert event on the subform, but there can be many order items per order and it will run before I have finished adding order items. I tried it in the after insert event on the master form, but I think that this will run the code too soon as at this stage the order details will not have been written to that table.
View 4 Replies
View Related
May 12, 2015
I have a form which has a list of stock we have available to sell. I can then create an invoice and put in the relevant details. I then can select a product and it will add it to the invoice (which is in the form of a subform). Then the user confirms the quantity of each product. This is where the problems start....
I want an update query which will subtract the quantity from the overall stock level and give me the new stock level. This is easy to do if the invoice is for only 1 product. But I it doesn't work if there is more than one product in the invoice. If the first product has a quantity of 2 being bought, it will subtract 2 for every product.
View 6 Replies
View Related
May 20, 2005
Hi
I am a relatively new user to access and wondered if you could help me with a stock control problem.
I have created an update query (qryUpdateQuantityinStock) with the below fields:
QuantityinStock
tblProduct
Update: [QuantityinStock]-Forms!frmCustomerOrder![frmProductOrderLine Subform].Form!Quantity
ProductNumber
tblPrdocut
Criteria: [Forms]![frmCustomerOrder]![frmProductOrderLine Subform].[Form]![ProductNumber]
When the user enters the quantity they require into the orders form I would like to deduct it from the quantityinstock field in the products table.
The above query does work if you add the following code to the beforeupdate event of the orders subform:
If Me.NewRecord Then
DoCmd.SetWarnings False
DoCmd.openquery "qryupdateQuantityinStock"
DoCmd. Setwarnings True
End If
Although I wanted the query to run from a button (confirm order) on the order form so that the user decides when to deduct the stock.
I cannot put a button onto the orders subfrom where the query should run from and if I place the code on a button on the main orders form it only deducts one order (one product) and not all products on the order.
Can you help me find a way to run the query from a button?
I have read through the threads and found lots of information on stock control, although have not yet found anything similar to this problem. I understand that this is not the correct way to do stock control, although my database is for academic purposes and only requires a simple stock control system.
Any help would be greatly appreciated (sorry for the long post!). :)
Many thanks
View 1 Replies
View Related
Aug 7, 2013
We have our access database with a bit of a messy structure?
We use our database to record sizes of our product. I am hoping to improve it by adding the items we have in stock to prevent manufacturing more.
Basically our main database called "Make & Model 1" has a list of various makes and model numbers, each model number lists various information needed to manufacture a replacement part.
Customers order the part and these part details are entered into a table called "Order Detail" What I would like to happen is that when the details are entered a calculated field adds the data entered to a text string. I need to match the text string to the same text string in a table called "stock" as there could be one part that matches hundreds of models.
Basically the "profile" "Colour" "height" and "width" make the string and this is what I need to match and tick a box / populate the number of items in stock. Eventually I would like this to reduce by the amount ordered but lets do one step at a time.
I am using Access 2010
View 1 Replies
View Related
Mar 21, 2006
hello, i am a newbie
pls hlp me:
how to do this, with access :
date......|custumer...|out.........|stock
3/3/2006|...............|.............|100
5/3/2006|.anna.......|25..........|75
5/3/2006|.bernard....|50..........|25
can you help me?
thanks
View 8 Replies
View Related
Apr 18, 2006
Have a fairly simple scenario I hope someone can help me carry out.
I am focussing on two tables: Sales and Stock
Stock contains information on each product
and includes a 'currentStock' field which is decremented with each sale.
Sales records each sale with date and stock id.
I want one action to update both tables, what would be the best method?
Thanks
View 4 Replies
View Related
Apr 19, 2007
Hi All
I am seeking the solution to a problem with calculating stock adjustments
We use a job code which comprises several stock items and labour which = a $ value
I have a form "work in progress" where I enter all the job details including the job code, the aim is to reduce the individual stock items from a table "Stock" when the job code is entered.
JobCode 25WE = 1 x power point, 25metre cable , 9 x clips
I have tried several methods but to no avail, is there a way of doing this
Thanks in advance
GeoffK
View 1 Replies
View Related
Oct 16, 2007
Hi hope someone can help. Thankyou in advance.
I have a small database for my warehouse that sells washing machines, fridges etc.
I am trying to get a grip on the stock control.
What I am looking for when a order is filled out on our order form is the stock number will reduce by the amount sold. Not sure how to make this happen.
I have the number of units in stock under the products table
ProductsID PK
ModelNumber
UnitPriice
Description
QuantityInStock
Sounds simple but for some reason I cannot figure it out.
Thanks
Kim
View 1 Replies
View Related
Feb 7, 2007
hello, i am new to this forum. i have been struggling on a project i am doing where i am tring to calculate the stock levles and keep track of them along with the re order level
i have calculated the current stock levlel, in a query called "stock level 3"
Here the total amount prodcuts sold are taken from the field called stock level.
Here is a break down of my original tables
CUSTOMER
Cu ID
And all their personal details such as contact details
CUSTOMER ORDER
This is where the order takes place and contains the following information
Order_ID
Cu_ID
Staff_ID
Order_Date
Required_Date
Recievied_Date
Order_Total
Delivery
Delivery costs
Overall discount
Type-Of_Purchase
Paid
Money_Owed
Final Costs
CUSTOMER ORDER DETAILS
Here is the specific details of one order.
Order_ID
Prod_ID
Qty_on_Order
Discount
Line_Total
PRODUCTS
Prod_ID
Supp_ID
Product_Name
Product_Type
Prod_ Unit_Details
Qty_in_Stock
Re_Order_level
Price
Unit_Cost
Manufacturer_ID
Shelf_Life
SUPPLIER TABLE – with detaisl e.g Supp Id and account number
SUPPLIER ORDER
Supp_OrderID
Supp_ID
Date_Ordered
Date_Required
S_Delivery_Date
S_Order_Total
Money_Owed Delivery
Driver
Supp_Credit_ Limit
Overall discount
VAT
Type-Of_Purchase
S_Paid
Supp_Final_Costs
SUPPLIER ORDER DETAILS
Supp_OrderID
Prod_ID
Qty
Unit_Detials
Qty_Recieved
Discount
Line_Total
QUERIES
•In the query “All customer order details”, I have worked out the line total for each prodcut with in one order. I have timed the Qty_on_Order (customer order details table) by price (in the produt table) and by the discount(customer order details) this then gives the line total
Now to update the stock level I made a new query called “stock level Query”, where I added up the total number of each products sold, by adding the “Qty_on_Order” from the query “All customer order details”
I then made a new query called “stock level 2” where I calculated the amount left on the shelves by taking “SumOfQty_on_Order” of each product from “Qty_in_Stock” (form prodcuts table) to produce the field called “Qty_in_Stock”
I then made a new query called “stock level 3” where I inserted the same fields but an extra field called “Re_Order_level” for some reason could not add this to the query "stock level 2"
So far the system can add up the total number of products sold, for all the orders, and then automatically calculate the Qty_ in_ stock by taking away the “SumOfQty_on_Order” once a new order is made.
I then made forms where I go in my form to “customer”" and then to “making new order”.
Below is a sub form called “all customer order details sub form” . Here orders are made with defining "Qty_on_Order
I want to make a system which calculates for me i have ordered more stock then there is.
Howcan i now when i make sure when i place a new order in the query"All customer order details" , that if i order above the number of "Qty_in_Stock" (from query srock level 3), from the query “stock level 3”, that when I order more products then ““Qty_in_Stock”, in the query “stock level 3”, does not allow me to enter this and if it is possible please can you insert error messages on it.
I do not know if this helps .- I can not make the “Qty_in_Stock”, to appear in the actual table “Products”.
At the moment I can only update this in the query “stock level 3”. I am not sure if I need this so I can then set re order level which then comes up with an error message telling me when the re order level has been reached. -
As later on I will need to add on any products brought in from the suppliers to the Qty_in_Stock”, in the products table to be automatically updated.
"stock level 3".
here are the fields
Code:
Prod_ID (Products)
Prod_Name (Products)
SumOfQty_on_Order (from "stock level Query")
Qty_in_Stock: Products.Qty_in_Stock-[SumOFQTY_on_Order]
Re_Order_level (Products)
here are both sql from both queries
All customer order details
Code:
SELECT [Customer Order Details].Prod_ID,
Products.Price,
[Customer Order Details].Qty_on_Order,
[Customer Order Details].Discount,
(Products.Price*[Qty_on_Order]*
(1-[Discount])/100)*100 AS Line_Total,
[Customer Order Details].Order_ID,
Products.Prod_Name,
Products.Prod_Type,
[Customer Order].Cu_ID
FROM [Customer Order] INNER JOIN
(Products INNER JOIN [Customer Order Details]
ON Products.Prod_ID=[Customer Order Details].Prod_ID)
ON [Customer Order].Order_ID=[Customer Order Details].Order_ID
ORDER BY [Customer Order Details].Order_ID;
stock level 3
Code:
SELECT [All customer order details].Prod_ID,
Products.Prod_Name, [stock level Query].
SumOfQty_on_Order,
Products.Qty_in_Stock-[SumOFQTY_on_Order] AS Qty_in_Stock,
Products.Re_Order_level,Products.Prod_Name,
[stock level Query].
FROM (Products INNER JOIN [All customer order details]
ON Products.Prod_ID = [All customer order details].Prod_ID)
INNER JOIN [stock level Query]
ON Products.Prod_ID = [stock level Query].Prod_ID
GROUP BY [All customer order details].Prod_ID,
SumOfQty_on_Order,
Products.Qty_in_Stock-[SumOFQTY_on_Order],
Products.Re_Order_level;
please may help me and gice m,e soem tips it will be much appreciated or website/downloads.
Thankyou for your help, and if you find easier ways to do this can you help me.
Thank you so muh.
View 1 Replies
View Related
Apr 30, 2007
Hi
Can anyone help.
I have a table with units in stock eg. beans 27 tins. An order is placed for 6 tins of beans. How can I reduce the amount of stock in my stock table.
Hope someone can help.
Thanks:confused:
View 4 Replies
View Related
Sep 6, 2007
Hi,
I am creating a database which keeps control of stock. Each item is scanned in with a barcode scanner and given a unique stock number. The Manufacturer, System, Subsystem, Location and Classification is then added.
When a customer phones, and orders a product i.e. a camera, a customer order number can be added to the item in stock. The problem with this is that if the customer is ordering a number of items, and other items are not in stock yet, this item needs to be seen as unavailable. Due to large amounts of stock, i do not want to allocate the customer a specific item with stock number, but just allocate one of the many items of that sort. I do not want to allocate a specific item as when you go to collect the item from the stock room, every item of this type would have to be checked to find the specific stock number, which would be timely and difficult.
I tried using a make table query, which copies the information from the stock table, except the stock number, and so with this list, i will have a list of every item. I can then produce a table which shows me how many of each type of item is available. The problem is, this needs to update every time more stock is added, but i also need to be able to allocate a number of the items, and this becomes unavailable stock. At the moment, each time the query is run, the information is overwritten.
Please can someone help me if they can think of a better idea of how to do this! Im not that hot on programming, and so a in depth description of what to do would be great!
Emily
View 3 Replies
View Related
Sep 11, 2007
Query does not return all stock. In basic form the database has 3 tables. One is “stock” another is “stock in” and the third is “stock out”.
I want to run a query using all three tables so that I can take stock out away from stock in. The problem is the column stock in (in the query result) only shows stock that has a value in stock out. If any stock items have not had stock go out the stock does not appear in the query result. I want stock in to show all items regardless if stock for that item has gone out. I think this can be done but I cannot figure it out.
Sorry if I have not made myself clear.
Please Help. Thank you Keith Hawes.
:(
View 7 Replies
View Related
Oct 26, 2006
Looking to see if anyone has any smart ideas?
I have a stock database and it is consisting of three parts. The first part is the parent. This is the complete item that people can buy. E.g a car.
The next level is a sub-parent. The pieces that go into making the car like the engine and wheels.
The lowest level is the parts that go into making these. Like the rubber, nuts and bolts.
I need it so that orders can be taken for the top level and for spares, the second level. When this occurs I need it so that the data base will take out the stock from the bottom level to build the pices for the higher levels.
E.g 1 car ordered is equal to 1 engine, 4 wheels and 1 car body.
1 engine is equal to 30 bolts and 50 kg of metal from stck and so on.
Then the same again but, with people only buying the engine.
How is it best that I relate through the tables to change the quantity of stock from the top to the bottom?
View 2 Replies
View Related
Oct 26, 2006
Looking to see if anyone has any smart ideas?
I have a stock database and it is consisting of three parts. The first part is the parent. This is the complete item that people can buy. E.g a car.
The next level is a sub-parent. The pieces that go into making the car like the engine and wheels.
The lowest level is the parts that go into making these. Like the rubber, nuts and bolts.
I need it so that orders can be taken for the top level and for spares, the second level. When this occurs I need it so that the data base will take out the stock from the bottom level to build the pices for the higher levels.
E.g 1 car ordered is equal to 1 engine, 4 wheels and 1 car body.
1 engine is equal to 30 bolts and 50 kg of metal from stck and so on.
Then the same again but, with people only buying the engine.
How is it best that I relate through the tables to change the quantity of stock from the top to the bottom?
View 1 Replies
View Related
Dec 20, 2004
Hey Guys.
I have a sub form based on an order table. I had another table which lists products which customers can buy. In the products table I have a field named "units on order" which I'd like to update when an order is made in the form and a field named "units in stock" which upon the user opening the order form again and clicking a button to say that particular order has been delievered, the "units in stock field in the products table will had added the quantity chosen in the order form.
Any suggestions?
Thanks
Tempest
View 14 Replies
View Related
Aug 12, 2004
I have a database of equipment that is hired out. When the equipment is hired out the date is recorded and when it is returned the date is also recorded. That same piece of equipment may then be hired out again. What I want to be able to know is what equipment I still have in stock at the base. I have tried many different attempts of trying to get this to work but I don't seem to be having much success. Could someone please tell me how I can do this.
View 2 Replies
View Related
Sep 11, 2007
Query does not return all stock.
In basic form the database has 3 tables. One is “stock” another is “stock in” and the third is “stock out”.
I want to run a query using all three tables so that I can take stock out away from stock in. The problem is the column stock in (in the query result) only shows stock that has a value in stock out. If any stock items have not had stock go out the stock does not appear in the query result. I want stock in to show all items regardless if stock for that item has gone out.
I think this can be done but I cannot figure it out.
Sorry if I have not made myself clear.
Please Help. Thank you Keith Hawes.
View 2 Replies
View Related
Aug 25, 2005
I have a database for taking orders for books, leaflets etc, what i want to do is this:
When I receive a delivery of stock I enter how many of each ‘Product’ I have received, I then want Access to allocate these to the various orders to make then ready for collection.
For example, if five people have all ordered 3 of ‘Product Guide’ and I only receive 15 then the first three to order are allocated three each.
Not got a clue how to do this, guessing I’ll need to code it?
Hope it makes sense
View 4 Replies
View Related
Nov 2, 2005
I am looking to design a simple stock database for work. This is just an idea at the moment, unless I can get my head round this problem.
I have throught that I can have 3 tables
NEW STOCK(this is where you enter all new and existing stock is)
USED STOCK(this is where all the stock that has been sold used for whatever reason is entered)
ACTUAL STOCK(this is where the items in NEW STOCK have the items sold in USED STOCK deducted from them to give you the ACTUAL STOCK)
PLEASE HELP ME, am I completely on the wrong line or can I do this?
Would be good if I didnt have to use SQL, VBA etc...
View 2 Replies
View Related
Nov 9, 2005
Hi, I have a data base that is used to keep track of stock level. When i issue a stock item and move to the next record I would like the stock level to be reduced by 1, any ideas.
View 2 Replies
View Related
Jul 7, 2006
Hi guys,
Before you say anything, yes i have spent endless hours searching the forums for what im trying to find. The simple fact being that i can't find anything that fits my situation or i simply can't understand it.
Heres the problem -
I've been given the task, by a family member, to design and build a database. The database is for his carpet shop/warehouse. Being of such a business the warehouse always has stock coming in and going out. The manager can't keep track quikcly enough.
He therfore needs -
- A database of products in stock
- A history of old stock and who it was sold to (which customer/invoice)
- An easy, user freindly, way of entering stock as quickly as possible as it comes in
- A form for orders, which, in the top half the customer information is entered and in the bottom half a products/stock search by the stock ID/number to find the products they are buying. Also a subtotal/total box.
- The stock needs to be deducted from the stock/products list when it is bought and goes into a history database/table.
- Reports based on Orders, with only relevant information, for customer invoices.
- A stock search facility where all stock, present and past, can be searched to see what is most popular according to manafacturer, colour, quality etc.
It is not a shop/EPOS application that i am trying to create. Its simply a stock control system which, as a bi-product, produces invoices and reports etc. It goes without saying that, as staff members don;t have extensive computer knowledge, the whole database must very user freindly and simple and clear etc.
Now MY problem -
I can produce simple databases and can set up straight forward queries/reports etc. But the whole stock update thing really confuses me.
- i dont know what the set-up/design of the database should be
- i dont know how to do the stock update thingy
- i dont know how to create a search facility for products being purchased
- i dont know how to move the stock from the stock table into a history table with the customer/invoice information
- i dont know, qithout creating 10 different queries, how to make a stock search facility which the user can determine the criteria for searching
- i dont know how to make the whole thing look simple and user friendly!
What im looing for guys is for anyone to point me in the right direction. i Might sound lazy just asking for people to help a bit but i have spent weeks trying different things out, using examples, reading books, EVERYTHING. It all seems complicated until someone explains it to me. I dont mind doing it all myself but i dont know where or how to start. Can anyone suggest a suitable design/set-up? Can anyone provide a sample/example i could look at that is very similar? Can anyone help in any ways what so ever. I thought i was fairly good with computers but this proves that when it comes to advanced access im just a noob. if anyone can help a guy in need what so ever please do.
you all sounded really helpfull in other posts and i hope you can do the same for me. please remeber im a bit of a novice so be gentle! Thanks in advance for any help that you can provide me with.
All the best, James :)
View 7 Replies
View Related
