Inventory Database

[chrisg33]

Hello so i am a new access user and am tasked with creating a databse to control inventory coming in, inventory thats in the shop, and inventory going out. The main thing is that we are assembling a 50 part assembly. Is there a way to create the main assemblies in a seperate table then just say okay 3 assemblies are going out and it will reflect all of the componenets on the inventory stock sheet?

[CJ_London]

in principle and if designed right, then yes. What that design looks like depends on your business - not an exhaustive list but

can a component be used in more than one assembly?
do you carry stock of assemblies and components in your shop. i.e. you get an order for an assembly and then you order in the components
Are components interchangeable from different suppliers?
Can components have alternatives i.e. if a blue widget is not available, use a green one
when an assembly is ordered by a customer, are the required components reserved automatically?
do you sell components as well as assemblies
do components have a shelf life
do you make assemblies for stock, or only to order.
do you have sub assemblies?
how do you treat a partially completed assembly?

[chrisg33]

Yes to almost all of those.
The assemblies are built to order as we are doing assembly for a client so when it goes out of our door it is billed. Almost all of the componenets are used in various assemblies only the quanitity of those said coponents changes. I Know how to do this in excel but our files are getting to long and confusing to keep up with out orders and inventory

[CJ_London]

bit a of a vague response for a definitive answer

you will need a number of tables

tblItems
ItemPK
ItemName
itemType - optional - assembly/sub assembly/component


tblAssemblies
AssemblyPK
AssemblyItemFK - link to tblItems for the name of the assembly
ComponentItemFK - link to tblItems for each component
Quantity

tblOrders
OrderPK
OrderDate
AssemblyFK
Quantity
DespatchDate

will get you as far as reflecting assemblies going out and outstanding assemblies for dispatch. It only reflects your stock sheet on the assumption you start with no component stock, get an assembly order, you order and receive all components instantly (in which case your stock is the stock of components for that assembly) and is removed from stock when dispatched.

beyond that, insufficient information to provide a more detailed answer. You will presumably need tables for suppliers and customers, perhaps another table for supplier product codes. Perhaps more for managing part assemblies, components received, component returns, assembly returns/repairs, multiple assembly orders, stock take adjustments etc

[chrisg33]

I don't know if this will make sense. So I first I will build a table with all of the components that is all the components that will go into lamps and stay in our active inventory. When we receive a components I will specify that in the INVENTORY IN so I can keep track of all shipments received. When something gets entered into this it will update the INVENTORY ACTIVE (Don't know fi this would be a query or a table). Then I will build the assemblies in the example shown I have two assembles (1 and 2) which are composed of ABC and CDE respectively with different quantities of each needed in each assembly instance. Then when we ship out a assembly the INVENTORY ACTIVE would reflect this shipment going out.
If you could keep helping me I could link the excel file we currently use as an example.

[Dave14867]

Yes, all of what you want can be done. I designed a database for exactly what you are doing, I put in Purchase Orders when parts are ordered, recieve the parts when they come in and assign a Lot # for the parts if required, then when the assemblies are built, I relieve the inventory of all of the parts in that assembly and and put in what lot of the individual parts were consumed if available/required.

Dave

[chrisg33]

That is exactly ehat i am looking for. To be frank i am about to give up as i dont even know where to start. Is there a way to Send a blank datbase such as the premade templates? I could paypal you. if it works for my application

[Dave14867]

I have 28 tables in my design but alot of them have very little consequence. There are 8 main tables that take care of everything that you would need.

Dave

[Dave14867]

I wouldn't want any payment but I will assist you in getting started and you can take it from there if you like. I just want to return the help I got from here when I was getting Started.

[chrisg33]

Hi dave i believe i sent you a private message dont know if i did it right it seemed to quote something.

[Dave14867]

The Main tables are as follows

tblInventoryTransactions
TransactionDate
ProductID
PurchaseOrderID
SalesOrderID
DHRID
PartNum
LotNum
IncLotNum
VendorLotNum
StockDate
SerialNum
TransDescription
UnitPrice
UnitsOrdered
UnitsReceived
UnitsUsed
CycleCountCorrection
UnitsOnNcr
NCRNum
OUM
PriceEa
ExtPrice
ExpDate
DhrLot
WorkOrderNum
DateRequired
DhrOrder
Notes
PartLocation

This table has the most fields, I choose to keep all of these items in 1 table, some break it up into 2 or more tables.

Dave