MS ACCESS :: Manufacturing Inventory Database

Inventory Database Help
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.

Thanks in advance.

View Replies ! View Related

Music Inventory Database
hello, i am looking for a music inventory database, anybody any idea where i might get one
cheers
phil

View Replies ! View Related

PC Inventory And Allocation Database
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.

View Replies ! View Related

Inventory Control Database Help
Hi

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

Thanks

Marcus

View Replies ! View Related

Access 2007: Inventory Database
Hey all, long-time-lurker-first-time-poster.

I'm setting up a database for work, which is not quite an antique store, but for our purposes, let's assume that it is an antique store. We often have items that sit for a long time and cost several thousand dollars. We have a very, very slow moving inventory. The way we have been managing it is on paper, but now we want to move to a computer solution. We have about 500 items and we'd like to be able to rapidly look through pictures of each item and maybe click on one to bring up a page that has more info on each item. We need to be able to view the database from several computers at once, and ideally make changes. Right now we have everything set up as a SharePoint list on our Windows Server 2003 machine.

I'd started by making a database and adding photos as attachments which works ok in theory, but it is very very slow. I then tried to reference each picture as a link, using VBA to swap the pictures using this method: http://support.microsoft.com/kb/285820

But the problem with that is we want to be able to look at many items at once, like in a continuous report, but it loads the same image for every picture frame! It isn't until i click on each record that the photo updates.

Does anyone have any general advice for me on the best way to set it up, what tables I might use or any other set up advice? I have the data basically inputted, I can bash it into any format you think is appropriate, so just let me know!

Thanks!

View Replies ! View Related

Table Organization For Inventory Database
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.

Any input would be appreciated!

View Replies ! View Related

Newbie Needs Help With Tracking Inventory In Orders Database
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!!!!

View Replies ! View Related

Importing Daily Excel Spreadsheets Into Item Inventory Access Database
Hello,

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.

View Replies ! View Related

Inventory
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?

View Replies ! View Related

Inventory DB
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

View Replies ! View Related

HELP With Inventory
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

View Replies ! View Related

Inventory
dear all

ı would like to calculate moving weighted avarage cost for inventory application

how to calculate method for query or report

please support

thanks

View Replies ! View Related

Inventory DB Need Help Please
Hello this is my first post :)

well im making a Inventory DB and having a little trouble with adding and subtracting the units. This is what i need:

This is the command button

Private Sub Finish_PO_Click()
On Error GoTo Err_Finish_PO_Click

DoCmd.GoToRecord , , acNewRec

Exit_Finish_PO_Click:
Exit Sub

Err_Finish_PO_Click:
MsgBox Err.Description
Resume Exit_Finish_PO_Click

End Sub

I need to subtract this.....

Private Sub Units_In_Stock_BeforeUpdate(Cancel As Integer)

End Sub

From.....

Private Sub Units_Needed_BeforeUpdate(Cancel As Integer)

End Sub

Keep in mind i am a bit of a beginner but im a fast learner. Can anyone please help me with this

View Replies ! View Related

Inventory DB
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?

View Replies ! View Related

Viewing Inventory
Hi all,

You will see that I am new to Dbs but have touched the subject many moons ago.

Anyway, what I want to achieve is to build a dbs in Access 2003 to "view" inventory. For example:

Vendor x has 1000 products
Vendor y has 1000 products
Vendor z has 1000 products

All products are similar by description, but what I want is a dbs where workers can perform a search for a particular product, and all vendors that carry a similar product will display their items and its RRP price. This process will make it easier for workers to recommend a product based on description or price as per customer's inquiry.

I assume that I will need a few tables:
Vendors
- Contact details
Products
- Part Number
- RRP
- Description
- Associated Vendor (Perhaps relating to the vendor table)

I would like the information to be able to be displayed on a web page as the creaming on the cake.

I know I am asking for allot, but I have put some thoughts into it, but know that there is allot involved. I would like to do it myself so I can better learn Access better also.

Any suggestions or assistance would be greatly appreciated?

View Replies ! View Related

Inventory By Location
Hi all.

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?

Table Design
-=-=-=-=-=-=-

tblStockTake
StockTakeID
ProductID
Date

tblStockTakeDetails
StockTakeDetailsID
StockTakeID
Quantity
Location

View Replies ! View Related

Inventory Table
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.

Category
Charger,BB Data Only, BB Phone, Broadband Wireless Card, Cellular Phone, Laptop, Laptop Accessory

BB Data Only
Device, Model#,Serial#,IMEI,PIN#,Deactivated

BB Phone
Device,Model#, Phone,#IMEI, PIN#, Deactivated

Chargers
Device, MTHMID, Type of Charger

Laptop
Device, MTHMID, Manufacturer, Model#, Serial#

I also have tables for manufactures and service provider, can i add a lookup to the service provider table to the bbphone table

basically i want to create a form that will change the information requested based on the type of device selected.

I'm confused about how to setup my main table, and the relationships between the tables.. please help

View Replies ! View Related

Inventory Reconcile
We have to separate the inventory count up into Lot and Part because some of the parts were mislabeled with a bad Lot#. What we want is a way to count parts using either method, depending on whether the Lot # is correct. In a bin, you might see an odd amount of parts loose, and a few bags labeled with the correct Lot#. The handheld can be set to count either by Lot# or by Part# and creates separate records for each.

I then have 3 tables to compare and sort.

Table 1 contains Part#, Lot#, and Qty. Part# is a one to many relation to lot#, ie you can have more than one lot# for a single part#. There are entries which do not have entered qty, ie lot#'s which we are sold out of.

Table 2 contains the inventory from a handheld scanner, created using the Lot#, qty and Date.
Table 3 does the same thing using the Part#, qty and Date.

I want to weed out zero Qty. Lot# entries (easy) and also where the Lot# is counted by Table 2. The idea is to determine which Lot# is being counted by part#.

Also, I will need to see whether the counts for the inventory is correct, so this would look like a reconcile report when it's done.

Thanks in advance.

View Replies ! View Related

Inventory Queries Help
I am designing a database for my company and I have it almost finished, with only a few small annoyances, I have it set up to show what I have in stock by its PO# (just a # to keep track of it) I filtered it to not show anything that does not have one using IS NOT NULL for criteria, but I also have it showing Units On Order (basically whats in stock) and Units Commited (the parts committed to jobs) I would like to get it to also filter out all the ones where these two #'s are the same as I would not have any On Order parts left if they were all Committed, so what I am asking is how can I get it to only show the ones that are purly in stock and show the values of whats left after these two have been subtracted. If you need anymore info please let me know.

View Replies ! View Related

Autoupdating Inventory Help
hiya!

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.

any help will be appreciated!

View Replies ! View Related

Inventory Supplies
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

View Replies ! View Related

Inventory Pallet Control?
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.

Thanks

View Replies ! View Related

Stock/Inventory Control - Help.
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 Replies ! View Related

Inventory Remaining Field
I have created a db that tracks inventory on hand and inventory used. I created a query that calculated inventory left on hand; however, everytime I run the query, it asked me to type the value in inventory used (2) before it calculates the Field "Inventory left in stock. I dont want to have to lookup what amount is used for each inventory item. Can anyone tell me what my expression is missing? [inventory in stock]-[inventory Used]

View Replies ! View Related

Inventory Totals (sum Query)
I have an inventory table with the following feilds

InventoryTransactionID
ProductID
UnitsReceived
UnitsSold
UnitsShrinkage

I would like to create a query to give me an inventory for each product that i can reference to in a UnitsInStock control in a Product form.

I have tried the following with no success. What am i doing wrong?

UnitsInStock : =Sum(nz([UnitsReceived])-nz([UnitsShrinkage])-nz([UnitsSold]))

I get this error "You tried to execute a query that does not include the specified expression <name> as part of an aggregate function." (Error 3122)

Thanks for any help you can give me.

View Replies ! View Related

Identify Oldest Inventory
I am trying to identify how long inventory has been in a specific location. The information that I have is the current inventory level and movements that have put the inventory there. Assuming FIFO I would like figure out the following.

ex: I have 4 movements with various dates AND I have 3 pieces currently on hand, how do identify the date of the last 3 movements (assuming that the one that has been moved out was the first one in)? Of course there is a long list of part #'s, on hand quantities and movements into the location so the logic should work for all.

Sample data

Part # Date Quantity
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
123 07/30/1999 1
456 09/30/1999 1
456 09/01/1999 1

Part # On Hand
123 3
456 1

the Result I would like to see:
123 09/30/1999 1
123 09/01/1999 1
123 08/30/1999 1
456 09/30/1999 1

Is there a way to do this?

Thanks
Pat

View Replies ! View Related

Retreiving Current Inventory
I'm trying to pull out the most current inventory of 15 items I track. When items break I input a new inventory for my system (a new record.) I input the date I entered the information so show the newest record. What I'm trying to produce is an output report that just shows systems 1-15 in their most current state without showing the old inventories. I've tried to filter by date and system number but keep getting the old ones. I've also tried to hide replicates. I'm sure there is an easy SQL code that I don't know about any ideas?

View Replies ! View Related

Inventory Query Calculations
Within my "Products" form, I have a "Transactions" subform whose records are
categorized in five different type of inventory transactions - let's say "A", "B", "C", "D", and "E".

I'm using linked queries (instead of DSum) to calculate the net inventory amount (A+B+C-D-E) per product - sort of a running total.

The problem is that transaction type "E" may not appear for a particular record!

Consequently, the query calculations omit those products without transaction
type "E".

How can I include all the "Products" records into my inventory query calculations even though a product may not contain all the transaction types in their records?

Thanks, in advance, for responding.

Tony:confused:

View Replies ! View Related