I used the Northwind database and modified it to suit my needs. This is basically just maintaining inventory, using the "Orders" form and "Orders Subform" to create records in the "Orders" and "Order Details" table which deduct quantities from the "products" table.
Our employees are allowed to use the "Orders" form to issue items from inventory, and this is the method I used for updating quantities in the "Products" table (method 1):
http://support.microsoft.com/kb/252813
Here's the problem: It is entirely possible that a transaction from inventory might be cancelled, and there is no way to replace the inventory levels in the "Products" table, other than doing it manually.
I would prefer a method of maintaining inventory levels in the "Products" table based on current records in the "Order Details" table, whereas, I could merely open the "Order Details" table, delete the canceled transaction (record), and the quantities in the "Products" table would be automatically adjusted.
It doesn't seem like this should be too difficult, I just need a point in the right direction on how it should be done.
I have been struggling with a problem I thought was simple. However I am unable to solve it. I have searched the forum with no luck.
I have three separate tables that have somewhat similar structures. Each have a Quantity in a specific column that I would like to sum across all three tables. The tables contain hourly values for (say) one week (168 values for each contract type). The table structure is as follows:
T1: Contract_key1 Effective_Date Quan1
T2: Contract_key2 Effective_Date Quan2
T3: Unit_key CC_key Effective_Date Quan3
The length of T1 and T2 depends on how many contract types are involved. Table T3 depends on the number of units (Number of rows= 168*No. of contracts(units in T3)).
I would like to write a query (will be in VB eventually) that would give me the Sum(Quan1+Quan2+Quan3) for each hour accross all contracts and units. My output table should have only 168 rows (sum all quantities each hour). In the future I may want to sum for each contract, but currently I only need a single sum for each hour.
If this issue has already been dealt with on this forum please point me in the right direction.
Well, by now I'm sure you get the point. I want it to add all the quantities with their respective numbers and only show the total on the report. How would I do this?
I have a field, quantity, which i want to add all the quantities together to show a total. I want to do this using a query as i will use this total for other calculations. When in the query, i have selected "sum" in the totals column in the quantity. What do i need to do extra? I am not getting any results at the moment.
I am hoping to have the Product_Name as a drop down of all the products which are available and would like the Unit_Cost to automatically lookup the unit cost of the respective Product. Could someone please help me as i dont know how to do it, at the moment its a case of the individual having to look at the unit cost within the Product_Name drop down, remember it and then enter it lol
Also, how do I have the Line_Total as a calculated field of the Quantity*Unit_Cost? Do I put it within the Order Details table (which handles the many-to-many) or within a query?
Any help or links directing me to answer would be great Many thanks in advance
I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.
I am trying to build a an equation in a query field, which compares each quantity:
Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].
If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Variance of the above equation that would do it?
I need to run a condition that compares 2 field quantities together based on the same item. If they both equal the same then i want it to run a code and change a property for that record.
I will have a OrderQty and a Total receivedQty when these 2 equal each other then it updates the items Status to Received. What is the best way to accomplish this? in vba or queries/sql?
I have a transactions table which records a qty number (- or +) and references particular location details (location, room, rack, shelf etc). My question is how do I sum the qty for the same product at different locations. So my user sees that product A has a qty of X in location C and qty Y in location D. I can sum the transactions table qty which does give me a sum based on a single location but each location has multiple racks, shelves etc. I need to know the sum of transactions for the same product for exactly the same location, room rack etc
I have a form with subform . I want to calculate purchase quantity with criteria of product and quanties before sale date. If i use with specified date it gives correct result.but if i use field address it ignores date criteria
Table 1 has all the items I am trying to sell with sell by date after which I cannot sell this item. Then in Table 2 I have forecasted sales. So now I am trying to calculate stock consumption to see if I will be left with any stock that I cannot sell.
So now somehow I need to deduct sales forecast from my stock holding but it needs to go by date i.e. consume all stock for Item 1 with date 16/09 before moving to Item 1 with sale by date 23/09.
So based on the attached example, I can see that on 16/09 I will consume only 5 cases from sell by date 16/09 and another sale is 18/09. So that would give me information that I will be left with 95 items dated 16/09, which I cannot sell because they will be out of date.
Ideally I would like also to include the logic that if Item is out of date it would move to the next sell by date.
So in this case sale of Item 1 forecasted for 18/09 (94) would consume the whole stock (50) with date 23/09 and another 44 from date 01/10
For Item 2 I can see that units with Sell by date 30/09 will be consumed on 25/09 and I will start taking stock from next sell by date which is 14/10.
I'm curious, I need to set a database up to keep track of inventory for a new product. the product requires several materials to build the product. I would like to "forecast" so to speak, the amount of materials needed to maintain enough materials on hand for "x amount of product. Is this something I would use MS access for? Or would this be a better project for excel ? Any help would be appreciated.
If in fact ms access would work, could someone point me in the direction of an Inventory database tutorial?
I've been working on a new project, an Inventory DB..... Everything works well... BUT....... Now I am back to a design issue so I can deal with a FIFO inventory. I have searched the forum and every place else to try to see the prefered method for setting this up..... Can't find much. I have seen using serial numbers, or dates. Neither would work well for what I am looking at. I have thought of using a ID field for Inventory purchased.... Auto number... This would then show the first items in as the lowest number. But........ What is the common method for setting this up? Any examples around? Thanks
Could someone PLEASE point me in the right direction.... I've designed a couple of DB,s to handle inventory. But I am still stuck on bringing one option in. FIFO! My design for the FIFO will probable end up with the "purchases" table being set up with an "autonumber" field to record each purchase. My problem is still seeing some code to get the sale of items to loop thru these purchase "batches". If anyone knows of an example of this PLEASE post a link for me. Thanks Curtis
I made a seach on the internet and on the forum but I confused... Till Monday I should have ready I DB regarding the inventory of the warehouse in the company. Till now they were using the Filemaker v.3 that it's not the most appropriate way. So I started creating a db where I have to show:
what get out and what get in, the dates, and the person took them, and the place that the person went them, and of course the quantities but the inventory either gets bigger either smaller, and i have to fix it... with what receipt no and type the items get out or in and if we it as a sample
What I have done till now:
Products tbl product number (as listed on the computer), product number (according to the supplier), model number, supplier id, constructor id, description, comptible with (sometimes more than one product), price per item, qnty in stock, minimum stock, comments.
Customers tbl customer id, address, post code, phone number, city id
Cities city id, city
Suppliers supplier id, supplier, address, post code, phone number, city id
Constructors constructor id, constructor
The problem is how I add or subtract things from the products table?
I have been working on an Inventory Database over the past few weeks and I have run into a problem.
Currently I am designing the database to use the common way to calculate on-hand inventory levels (Latest Stocktake(cycle count) + Received - Used). The problem comes when I am trying to calculate this value.
A solution that seems to work is to take stocktake for a given product using the ProductID and date. I have a child table linked to this one to show the quantity and locations of the product. However, with this method, I will have to count the inventory for every location the product is in when the stocktake is taken. This method may work, but accidents may happen and an employee may forget to count both locations when taking stock. Then current inventory levels will be way off.
Is there an easier or more efficient way to do this?
I'm a newbie. I'm setting up a database to monitor inventory then allow users to make reservations from the items in inventory. I'm a little confused. I have the follow tables setup.
Im building an inventory database for my business, I am having trouble implanting an autoupdating stock level. This is how my database looks like:
I have InStock within the Table - Products I create sales orders through a form and that is recorded within a Table - inventory transactions. On this form there is a box to select the type of products and also a number box to input the number of units the customer purchases
I have created an update query with the following conditions Field InStock update to -[Products]![InStock]- Forms![Sales Orders Subform]![UnitsSold]
Field ProductName criteria is - Forms![Sales Orders Subform]![ProductName]
so in operation, i would select product A, enter the amount, that amount will then be automatically deducted from InStock for that particular product
I am stomped about what code to add to my afterupdate field... and where to add the afterupdate field.
I am wondering how in Acess do I create a Database with Drop Down Lists So I can search by Furniture Brand and Catagory and by Customer Name to get the furniture each person ordered.
Would this be a one to Many Relationship.
What kind of Coding would I need I wish there was a more simpalar program then Acess
I have some basic knowledge of Access and how it works but when it comes to the SQL formulas and some database structures I have not had enough experience. If someone could please guide me in the right direction with this I would appreciate it.
I have need to setup a database of all equipment in a small room so that I can keep inventory on it. Right now I have a database with three tables in it. An Items table that relates barcodes of an item to the actual item name. A Tech table that contains all the techs that would take from or replace something in the inventory of this room. And a Transactions table where it holds what item, who, and if it was taken or stocked.
What I need the most help with setting up right now is how do I use the transactions table to total up all uses and stocks of each unique item so that I can get an idea of what is actually supposed to be in the room at the moment.
I am try to make an access database to create pallet tags for some package and ingredients items that we purchase. What I would like to do is after the purchaser creates a PO in our purchasing system he would enter that information (SKU and total quantity)into a table. Someone from the warehouse would then go into this database, pull up the PO number and at that time Im guessing a query would be run on those original lines. This is where I know what I want access to do but don’t know how to get there. Assuming that we ordered 1000 of an item, Access would look at a table with the per pallet quantities and then create the appropriate amount of pallet tags and create a line in a table with the qty and the pallet number. I would then go back at a later date and enter a lot number to go with that pallet number.
Any Ideas? I have searched and have not found anything on this.
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.
I need to create a small inventory database tracking product in stock and "x" amount of raw material to create it.
Is there a way, using MS Access that I can track inventory of the product as well as update how much material was used to create the product?
I am new to access but I know how to create a database and enter the fields I want, eg: product id, model number, product color, in stock, re order, lead time.
My problem is associating another table to deduct/or add the amount of material based on totals.
In a nutshell, I want to track product inventory as well as track raw material in stock to create more.
Im creating a db to keep records of computers that have been assigned to persons in their various departments and also unassigned computers and spare parts.
problem:
For the machine specifications table should i put in the following fields as columns
Hard Drive 1 Hard Drive 2 Hard Drive 3 Optical Drive 1 Drive 2 Drive 3
or should i just create a field called Component and another field called type/size e.g. component would be hard drive or opticqal drive and Type/Size would be 60GB or DVD+RW
or should i just create individual tables to store the Hard drive info separate from the optical disk and separate from the memory.
The database is going to get really big which is why i wana structure it properly you r help would be really appreciated.
