Automatically Allocate Stock?
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 Replies
ADVERTISEMENT
Feb 7, 2014
I have a stockist which includes " stock number", " description"
When I use these parts to create a "bill of materials" for a job I am presently selecting the material and this data is going into the table and the stock number just goes in the form not the table, is there a way that when I select the "stock number" the material will still go in the table and the stock number stays on the form. or also go into the table
The reason I want to do this is because the stock number is easier to remember than the description ...
View 3 Replies
View Related
May 15, 2006
Hi All
I want a user to click a button and then it will assign the oldest record in the table to that particular user, by using their NT login.
Anyone got any suggestions?
Thanks
View 5 Replies
View Related
Jul 27, 2012
We have a model at my job which shows every job code one can do (there are only about 25 of these jobs) For example, let's say one job is called "Trade Settlements" and it's been estimated that we spend about 1000 minutes a day doing this particular job across the entire floor.
Well, I want to come up with a way to allocate the minutes of this to certain hour blocks and determining whether our group is over/understaff given the results.
So let's say I want, 30% of it to be done from 9-10 am, 10% done over the next 4 hour time blocks, and 2-3 pm for the remaining 30%.
Thus, we'd get something something like 300, 100, 100, 100, 100, 300 minutes and 0's for other time blocks.
These percentages would be input fields so if anyone types in a percentage, they get # of minutes spent for that hour on that job. Ultimately we would add these numbers up with other jobs and be able to easily update from there for any job type we wanted.
View 1 Replies
View Related
Nov 25, 2014
I've got a table [Devices] with some number fields that are already in there for another purpose.
In a different exercise I will manually collect more information from a layout drawing and give each piece of information I collect a unique reference(this could be as simple as a PK ref) and attach a "type" and "location" . This will be done randomly in a batch exercise any time.
I need to put the above into access and hold it until I choose to individually allocate these records to the number field that already exists in the [Devices] table.
I would like to add "say" a combo/List box in [Devices] where I could search for the PK Ref which when selected against a number would populate the [Devices] table with previously unallocated information. I would like to keep any combo/List box down to unallocated PK refs to make records easy to find. I guess this a kind of cut and paste exercise.
View 5 Replies
View Related
Aug 18, 2014
What is the best way to allocate a record number to the end user?
I have had problems where users have come back to me saying that both of them created the same record number at the same time and I am not sure why that is happening.
To be certain, I am using INSERT rather than UPDATE which was my initial concern (an update would just over write the previous record)
How can we check the last value, uniquely assign that value to a person at the point of creating the record and ensure that no one else can take it...
Is it possible that a laggy server would cause these issues? (as the code executes roughly the same time as another person also executes code, they overlap?)
View 1 Replies
View Related
Dec 1, 2014
On the surface this seems easy, but I am struggling. two tables
tbldata (four fields 6,270 rows)
custid 2015cellcode 2015qty 2015amt
12673 1243 100 1,000.00
12673 1250 200 2,000.00
etc etc
tblmntlyalloc (Three fields and 12 rows i.e. one row for each month)
2015mnth, 2015allocation, 2015wrkdays
jan 98 20
feb 93 19
etc etc etc
So in order to handle seasonality of sales, the sales department is given 1,200 points. you can then allocation any number of points (75 - 125) per month. But the total number of points must equal 1,200. So I created a table with the monthly allocation and workdays. I could hard code the % and work days into the formula like this
Jan1[2015amt]*(98/1200)) / 20
Feb1[2015amt]*(93/1200)) / 19
And this does work very well to get me my daily sales amount per month/wrkday
However I know sales will change the monthly allocation (still totals 1200) and HR could even have a change to the holiday schedule. So I want to utilize the table.
So my query brings in both tables, but they are not linked. And this is my formula.
Jan1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Feb1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Mar1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
Apr1[2015amt]*([2015allocation]/1200) / [2015wrkdays]
etc etc for 12 months
So I need it to find the value for Jan in the Jan formula, and Feb in the feb formula etc. I cannot determine the correct query formula.
View 8 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 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 1 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
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
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
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
Nov 21, 2006
We have a stock situation, where the balance on hand is calculated by taking into account receipts and issues/sales from a historic balance.
how do stock systems like this normally deal with stock that can be located in multiple locations.
sounds very hard to me!
Thanks in advance
View 10 Replies
View Related
