Add more tables and forms to an existed database with linked forms.

[namy77]

Good morning,



A while back a few folks had help me develop a database for my Wood Workshop. It currently has the Bills of Materials of my Products.

How can I add more forms to contribute to other attributes of these products? Such as prices or stock availability.

So, there would 2 additional forms that essentially looks something like this

Product Code	Price
A	$50.25
A1	$46.75

And:

Product Code	Available?	Stock
A	Yes	150
A1	No	0

Part Database1.zip


Thanks!

[aytee111]

Price would be added to the products table. This would be a suggested/base price only so you would need an additional table to carry orders, which would store the exact price that you charged a customer. Unless this is the purchase price?

An inventory transactions table to carry all transactions related to inventory - when a quantity is added to inventory. and when removed. There would be a new record for each transaction, the on-hand quantity will be a permanent query which sums the quantities.

[namy77]

There would only be one price. As long as there is a separate form to enter price for each (different users for this form vs the BOM form).

I was intending for the inventory to be static - for the users to enter or update on a regular basis, and not driven from transactions.

Thanks!

[aytee111]

The purpose of a database system is to be able to store data and keep track of it over time. Otherwise you might as well use Notepad! I disagree with both your ways of doing this, but it is your system.

[namy77]

The purpose of a database system is to be able to store data and keep track of it over time. Otherwise you might as well use Notepad! I disagree with both your ways of doing this, but it is your system.

The intention is that many people from different departments will have relevant access (says accounting to prices, engineering to BOM and warehouse to quantity). Thanks

[aytee111]

And do you think it is ok for the warehouse workers to change the quantity on hand any time they feel like it, with no audit trail, no accountability, no history of the transaction?

I get your issue to have different forms for each type of worker, you can create those forms as you wish. It makes no difference where you store the data, the users can share tables if that is they way you are designing it - having all the data for the product on one table.

[namy77]

Alright, I see what you are saying,where do I start to build a better database? Thanks!

[aytee111]

There are some good links to get you started here (post #7):
https://www.accessforums.net/showthread.php?t=65906

Before you learn how to create a database, I would suggest you do some reading on what a database is and the purpose for using one (as opposed to using Excel). I already mentioned the purpose of keeping history, auditability, tracability, etc on every bit of data that you company needs - being able to make decisions based on who did what when and how.