I have a query which is based on a tbl which has data on chemicals in storage. The query is based on two tbls as follows;
Main tbl
ProducdID
ChemicalName
Second tbl
Batch number
TransactionID
Transaction date
Purchased check box
Used check box,
expirydate
ProducdID
When a chemical is purchased a tick goes in the Purchased check box with the other relavant data. When a chemical is used a tick goes in the Used box along with other relevant data in the other fields. In essence each chemical will have only two records 1. a purchased record and 2.eventually a Used record.
I want the query to report on all chemical records which are still in stock. So it's not as simple as adding a True or False to either check boxes. Showing records which have ticks in the Purchased CB ,or not showing records with ticks in the Used CB, will still show a Chemical which has been Purchased but may have been used at a later date and is no longer in stock.
Any suggestions? I hope I haven't confused anyone with my problem?
Hy, I have this db where a user can purchase and item and total stock number needs to be updated.
I can calculate how many items are left but how to update the warehause table?
I can live without table updating, but if the same item is purchased multiple times my query return incorrect values as the number from which the formula subtracts is never changed.
I have a small stock control database built around a Northwind example. I am trying to build a query that displays items low on stock. On my products form I have: Min Order Level field (stored value) Units on Hand (calculated value from products subform (units received-units sold)).
Q1: Do I use a query to calculate/display this? Q2: How do I construct an expression to acheive this?
I just want to know how to manage items in set and individual item. Suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K and 5 pc set = A,B,C,D,F and 3 pc set = G,H,K
How should I design the table. Previously I designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
E.g. order is for 5 pc set = 3000
A=3000 B=3000 C=3000 D=3000 F=3000
Now I need to just say 5 pc set and it should be automatically populated. And also if order have combination of individual and set items.
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?
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
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
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
•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.
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.
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
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!
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. :(
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?
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?
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.
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.
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.
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?
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.
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.
1 how the stock control will work? I found similar posts saying to calculate the items sold and compare them with items in stock. When there is restocking the items in stock field will be increased?
2 i want to store the total amount paid from a customer in each order. I searched the posts for this case and the answer always says you shouldn't store calculated values but calculate them each time. In my case the prices for a product might change and user wants to know the total amount received from a customer for different purposes i.e discount, promotion e.t.c
I have an existing stock control database that has evolved over the years from the "Inventory Control" sample supplied with A97.
We (until now) supplied spares from one manufacturer. The spares items list was provided to us in an excel sheet and easy to append to a Access table.
We now want to supply spares for two new manufactures who also supply spares list in an excel sheet.
The problem: All 3 manufactures excel sheets are very different in layout format of number etc. and approximately 30,000 lines in total.
The question(s):
> Do I spend ages messing around copy/paste into one "super excel sheet" then append to access or some how have 3 tables (one per manuf.) suppying to one form?
>Can access handle 30.000 records in one table? Maybe a silly Q. I presume very slow on search?
