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 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.
I am trying to develop an inventory control database and would really appreciate some advice on the way I propose to develop the application
Inventory Control Database.
Outline for inventory control database.
The database will have the following tables:
Supply Table
Person Table
Supplied Table
On hand table
Orders Table
The Supply Table - will store the names of the stock items available
The persons table - will basically hold information on the person receiving the product/supplies
The Supplied Table - will hold information on who is being supplied, what is being supplied, who is the supplier, how much is supplied and when.
The On Hand table - will initially store the opening stock for all the stock items available. When an individual is given some of that stock, the amount of stock given will be subtracted from the on hand total, for that particular stock item.
The Orders Table – Will be the place where we will record all orders received. Once an order is received, this figure will be added to the On Hand Total for that particular item. So ultimately the on hand table should reflect initially the opening stock figure, then the opening stock figure plus any amounts ordered. I anticipate that after the initial opening stock figure is entered onto the system, the on hand table will be automatically populated by the orders table, and the site user will only need to manually update the On hand table in case of emergency, for example if a mistake is made on the order table.
I would really appreciate some help with the table design logic here. This is the first time that I have developed a stock ordering system, and would appreciate any advice on the over all design.
Initially, I want to build this as an access application then convert it to a web application ASP/SQL Server
I'm wrestling with a problem with a hardware and software database, on the software side. I'm trying to figure out where to store the license and media cost for a piece of software. Right now I have the following tables:
1. Software name (MS Word, Office, for example) 2. Software version, which also stores whether a license is required 3. Software license, which includes the license code and whether that license expires 4. Software inventory table, which stores the # of licenses we have available for installation. The purchase order (PO) # and date are currently here. 5. Software PO generating table. This is used when we need to reorder more licenses, or a new piece of software.
I need to figure out where to store the cost of the software, the cost of the license, how many people are covered by the license. I've been trying to decide which of the last three tables should store this information.
There are two issues: we need to keep a history of how much older versions of the software and its licenses cost, as well as be able to enter information to order new versions of the software and/or licenses.
I need to create an inventory database that will track customer owned inventory as it is received stored serviced and sent back to my customer. I am in the oil and gas industry and store large amounts of customer owned pipe. many joints will have the same part number but each joint is one of a kind in that it has a joint,heat, lot, and batch number. here is an example,
Job Name: Shell ex Part Number: 129001 Quantity: 1000
Each one of the thousand joints will have a combination of this information making it one of a kind.
My system needs to allow my users to receive jobs, record where we store the pipe in a rack location, pull the specific joints out of inventory as the customer calls for them. We will receive 1000 at a time but the customer calls for portions of that job over long periods of time..
I am developing a database for a friend and have all the relationships, tables, forms, queries and reports working well. I used the orders template to build this and just made a few changes. I have an orders form with an orders details subform that works beautifully. There is also customers table, employee table, products and inventory table. The inventory table has product ID, units in stock and reorder level as the only fields. I need an easy way to have the orders detail form decrease inventory as items are ordered and also some sort of way to enter restock. Can anyone offer advice that a beginner can understand in regard to this?
I wrote an update query that he can run that will subtract the qty ordered from the units on stock for orders with order date >= whatever date he enters, but if he enters a date he's previously updated it will update those records again. I think this is not a good way to go but the only way I could accomplish. I have him using the same order entry form with customer name Restock to add items to inventory by putting an if then else statement in the before mentioned update query. This just adds to inventory if customer name="Restock" else the qty ordered is inventory units in stock -qty ordered.
I'm sure someone can suggest a better way. A friend mentioned dsum function but can't explain how to apply it in this instance. Thanks so much!!!!
I am trying to create a simple store keeping In and Out inventory database using Access, I thought I had made it but looks like I am missing something here.
The store works on SRV (Store Receiving Voucher) and SIV (Store Issue Voucher). Products will be added based on SRV and will be issued out based on SIV. So far I have created the tables as you can see in the figure. One thing I am not understanding is where to keep the record of the Current Quantity of each product, lets say an Item has been added or issue out, it should be added or deducted accordingly from that specific products overall quantity. Right now I have a sample field within products table as you can see with the name QtyOnHand but that doesn't seem to be logical.
Im building an inventory database for my company and have the tables, relationships, forms and query's built but im struggling to figure out a way to make all the quantities move around when I have a purchase order or sales order that either adds to removes from inventory.
My database is fairly simple as all I need to do is track what is coming and going and what is the current levels of inventory. I have for instance a product table that has all the information on what products I have and what their part numbers, item number (primary key), description and so forth, separate customer tables and supplier tables, as well as an inventory table setup similar to the northwinds database. I used queries to combine my purchase order and purchase order detail tables and to combine the sales order and sales order detail tables to make sales orders and purchase orders through their respected forms and all is good in that sense but it doesn't move the inventory numbers just lists what is in each purchase Order/Sales order.
I want to make inventory fifo(first in first out) db. Example:
February 1- beginning balance : 800 unit @ 6$ per unit 4- received 200 unit @ 7$ per unit 10- received 200 unit @ 8$ per unit 11- issued 800 unit 12- received 400 unit @ 8$ per unit 20- issued 500 unit 25- returned 100 unit to storeroom to be recorded as latest issued prices 28- received 600 unit @ 9$ per unit
The report must be like pic attached
I dont know how to make transaction to do this....
I am not sure how complicated of a job this is, but we want to be able to import our daily recievals of inventory into our existing inventory database instead of entering it by hand. I try using the wizard, however when I get to "finish" it says there was an error and it was not imported.
I don't know much about access but I was hoping one of you could point me in the right direction.
I want to track inventory. All our items are tubes that are stored in boxes in a 10x10 grid. So I have a single access table with columns for tube ID, box ID, and position in the box (numbered 1-100). That works fine in terms of 'where can I find tube x'. However it's also useful to be able to look at the physical box & check that there are tubes where should be tubes, and empty spaces where there should be empty spaces. So for each box in the database, I want to print out a 10x10 grid, with the appropriate tube ID's in the appropriate place. Then I can easily check the layout in the database against the layout in the actual box.
I have a Inventory DB and i want to integrate scanning. So far i have a table called StockInfo, this table holds information about bar codes (device type, make, model). I have a form called Scan_Barcode, on this form I want to be able to scan a bar code into a text box (text0) and use a afterupdate and requery function to display the results onto a form called EnterInventory.
Not only just the results but I want certain columns from the query to display in certain text boxes on this form. I have a query called FindProduct. that filters the criteria by whats entered on the Scan_Barcode text box (text0). So when i scan a certain bar code i can get the query to display certain product information for the bar code. I just cant get the results onto the HarwareStock form.
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 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.
