Inventory Help

[Emily]

Hi Everyone,
I am sure this has been asked 1,000 times, but I can not seem to get
anywhere. I am making a database for my Jewelry Store. I have two types of
inventory, Part and Finished Products I have set-up tables and started on my
forms. My Tables are: (leaving out fields that do not matter for mt
questions)

Finished_Inventory
ID
Vendor
Quantity
etc...

Parts_Inventory
ID
Vendor
UOH(Units on Hand)
etc...

Finished_Inventory_Detail
Finished_Inventory_ID
Parts_ID
Quantity
Cost
etc...

I use parts to make finished items and well as purchase finished item. On
my finished inventory form I have a sub form to enter in any parts I may have
used. My problem is making Access take the parts I have used in the Finished
Inventory Sub table out of the Parts Inventory automatically.

I will also set up a Customer purchase table and I assume that I can set it
up the same way to take out of finished Inventory.

My next question is if I purchase X more of item A can I just go into that
item and adjust the quantity and cost with out have to set a vendor purchase
table.

I am learning still so please go easy on me. I thank you in advance for all
your help.

Emily

 

[Bob Quintal]

Hi Everyone,
I am sure this has been asked 1,000 times, but I can not seem
to get
anywhere. I am making a database for my Jewelry Store. I have
two types of inventory, Part and Finished Products I have set-up
tables and started on my forms. My Tables are: (leaving out
fields that do not matter for mt questions)

Finished_Inventory
ID
Vendor
Quantity
etc...

Parts_Inventory
ID
Vendor
UOH(Units on Hand)
etc...

Finished_Inventory_Detail
Finished_Inventory_ID
Parts_ID
Quantity
Cost
etc...

I use parts to make finished items and well as purchase finished
item. On my finished inventory form I have a sub form to enter in
any parts I may have used. My problem is making Access take the
parts I have used in the Finished Inventory Sub table out of the
Parts Inventory automatically.

I will also set up a Customer purchase table and I assume that I
can set it up the same way to take out of finished Inventory.

My next question is if I purchase X more of item A can I just go
into that item and adjust the quantity and cost with out have to
set a vendor purchase table.

I am learning still so please go easy on me. I thank you in
advance for all your help.

Emily

Emily,
I usually have to tell people asking questions that the do not have
enough tables. In your case, I think you have too many.

Inventory should be one table, because finished goods and purchased
goods are still inventory. Raw materials is also inventory.
Units on hand is a quantity. The vendor for Finished goods you made
yourself is Yourself.

The same thing applies to Sales. Goods used in making your stuff is
sold to Yourself. You can only sell goods from inventory.
So you add a field to the inventory table to indicate whether the row
is a receipt of goods (or Parts, and that includes finishing an item
you have made), or a sale.

That said, if you leave the inventory sold in the table, the
inventory on hand will always be the sum of quantity for that part id
from the inventory table minus the sum of quantity of that same part
id from the sales table. That's easily calculated with a query.

So I'd have a Vendors table giving Name, address, phone and ID,
a Customers table with the same info. You could even put them in one
table with a flag to indicate wheter the row is one, the other or
both.

I'd also have an inventory transactions column with an ID
dateTransaction, a Buy/Sell flag, itemID, quantity, cost,
sales_price, and a field to store the reference to the buy
transaction for when the row is a Sell type.

Your subform for the materials used is then based on the same table.

 

[Emily]

Thank you so much for your help. I have a follow up question. So I would
sell the part to my self. How would I than what item went to make the piece
in casr it needs to be duplicated. Would you suggest to have a field for
New Inventory_ID and make a relationship between them?

Thank you again so much. You have made this all make a lot more sense.

Emily

 

[Bob Quintal]

Thank you so much for your help. I have a follow up question. So
I would sell the part to my self. How would I than what item went
to make the piece in casr it needs to be duplicated. Would you
suggest to have a field for New Inventory_ID and make a
relationship between them?

Thank you again so much. You have made this all make a lot more
sense.

Emily

That field is the one I described in this paragraph, used to
reference the buy transaction.

so you buy a part, and it gets a row in the table. You 'sell'
yourself that part to use in a finished good, so it gets a new row in
the table as a sale, where you put the reference id of the part you
made as the buyer.

If you take 5 different parts to make into one new one, you make
6 new rows in the table, 1 buy row, then 5 sell rows with the SOLD TO
column referencing you 1st row, and the Reference showing the
original row for the original part.

A little example of the important fields

RowID Buy/Sell Item qty Vendor/Customer Reference
0033 Buy Diamond 3 DeVries
0034 Buy chain 12 Metal Supply
....
0040 buy Bracelet 1 Emily
0041 Sell Diamond 1 0040 0033
0042 Sell chain 2 0040 0034
....
0049 buy Bracelet 1 Emily
0050 Sell Diamond 1 0049 0033
0051 Sell chain 3 0049 0034
....
0077 Sell Bracelet 1 Bob 0040


Using the Reference field you can lookup what was used to make your
bracelet # 0040 1 diamond and 2 pieces of chain.

And you can calculate that you now have 1 diamond in inventory.
Buy Diamond +3
sell Diamond -1
sell Diamond -1

Bob