Creating a Database for inventory

[Haoming0]

Hi,
I am a fresh engineering graduate that has little expertise on using Access.
My family have a small local SME business and I will like to help upgrade the current inventory system through the implementation of access.
Ive been playing around with access for the pass few months learning through youtube and forums to do some simple stuff from tables to forms and functions like search textbox.

I am now stuck with a problem that is hard to find a exact solution, hoping anyone reading will be able to help me!

Basically the whole system is suppose to be able to capture:
Supplier info
Customer info
The transactions (IN/OUT of inventory)
Items (info)

Now the problem is that each item, for example:
6001
6002
6003
each have multiple brands like NTN, UBC, SKF etc. I will want to record the (quantity, selling pricing, purchasing price) for each item on each brand.


And also we have fixed selling price and also different selling price for different customer, same apply to purchasing price.

It just seems to me that there is alot of variables (not sure if its the correct term) which i find it difficult to identify the number of tables that i actually require that will reduce data redundancy. Or is that unavoidable.
Do i create a table for each brand? doesnt seem very practical to me !

Please teach me if you are free! thanks in advance !

[CJ_London]

I've no idea what item '6001' is or how that relates to brands, if you want help you will need to provide much more information.

As a general guide, prices change and selling prices do not relate to purchase prices, so these will need to be in separate tables. if you want to record a purchase price against a selling price you need to decide on what basis - a common one is FIFO (first in, first out). But what happens when you sell a quantity, some on the first price and some on the second? Another basis is standard pricing where you measure the difference between actual price and standard price. Typically your accountant should know what basis is being used.

Do i create a table for each brand?

definitely not


You will need a lot more tables. Get a packet of sticky notes, write on each one a 'field name' (customer name, product name, product code, selling price, discount etc) then stick them on a wall. The basic rule is that you can only have one field name, one stickie, they should not be repeated) now start to group them together. These groupings will eventually become your tables. You can then use bits of string to create your relationships - 'I'm creating an invoice, I can get the product name from here, the price from there, the customer name from this table, their address from that table'

Hopefully enough to get you started - good luck

[Micron]

They look like bearing numbers to me. In addition to the good advice I think you've identified a need for at least one junction table (research the term if it's not familiar).
For example, while you would have a table for items (parts), you would not have a field in that table for every supplier name for parts. You would have a suppliers table with supplier info only, but relating these two entities requires a junction table tblPartSuppliers. It would look something like

tblSupplier
SuplrID	Supplier
1	Acme
2	Bearings R Us
3	WeDooDat

tblParts
PartID	PartNo	Desc
1	6001
2	6504
3	7895
4	9851
5	2569

tblPartSuppliers
psID	PartNo	Supplier
1	3	6
2	3	7
3	3	8
4	3	1

By linking these tables in a query, you'd know that supplier 1 (Acme) is one of the suppliers who provides part ID3 (7895). The related fields in the other 2 tables provide the supplier name (from tblSupplier) and the part info. While you can use text primary keys instead of the PK fields (the ID fields) you must ensure the value will be unique. Names do not usually make for good PK fields.

You have chosen quite a complex project for your first db and most of the pitfalls are probably on the Access side. M$ has made it relatively easy to create something; very easy to do it poorly. That often makes people strive hard to make a silk purse out of a sow's ear (as the saying goes). IHMO, you can't do too much preliminary research on this. Maybe post back with a pic of your relationships when you have something for consideration. I'd recommend doing it on paper and scanning it.

[CJ_London]

To add to microns comments - you might think tblPartsSuppliers is the place to put the supplier price. But think carefully - prices change and may be subject to quantity or other discounts and you may also have shipping costs to contend with, not to mention tax.

[ArviLaanemets]

You want to track similar articles/items from different producers - define them as different articles/items. In case you need to keep track of similar articles/items of various brands as whole too, then in articles/items registry table add an ItemGroup field (and maybe you need then an additional table for ItemGroups too).

[orange]

Pay attention to the advice given. Your need for different prices for different selling and purchasing prices by Supplier/Buyer adds to the complexity. Further to Ajax's get a clear list of the outputs you need; then some sample data working with the model (sticky notes) run test scenarios against the model. see this for ideas
You need to get your requirements sorted out and tested before jumping into physical database.
Good luck