Designing Purchanses/orders/inventory Database help?

[Guest]

I have been fiddeling around with access for some time now, and still am
having trouble designing the database to meet my needs. I want to have a
database that tracks purchases by catagory (books, entertainment, etc, ).
Then I created a subform that showed the catagory details such as date,
merchant, amount, type of payment. This is where I have problems-for each
purchase order within that catagory I want to have a seperate Table/form
which shows each purchase order in detail: Product name, product type, units,
price per unit, sales tax, updated(if product was returned) and total for
that specific transaction, which then would change the purchase catagory
record total.
The next part of my database needs to be an inventory of products
(mostly thoses from the purchase orders, but may include other that were not
under purchases) , quantity, date purchased, merchant/merchants , and notes,
or details about the product. I tryed to create this by using an expense
database type design, and it is just not working. I also want to be able to
enter the data easily, via catagories, and subforms. Is there a more
effective and just all around better way to completing this? I am somewhat
proficient in access but still a beginner in many respects.

 

[Allen Browne]

When designing your database, It helps to separate out 2 things in your
thinking:
- the data structure (tables, fields, relationships)
- the interface (forms, reports)

In the tables you *really* need all the categories in the one set of tables
(i.e. not a different table for every category.) In the interface, you might
want to have a different form for each category (books in one form,
entertainment in another.) To separate from the one table for the different
forms is just a matter of creating queries with the criteria to limit them
to the category.

That allows the user to enter the data as they percieve it (different
categories), but you can still run reports on the transactions for a month
etc very simply (since the different categories are in the one table.)

If you have never created a database before, the place to begin would be to
open the Northwind sample database. Northwind.mdb installs onto your
database when you install Access. Open the Relationships window (Tools
menu), and see how the tables are designed and related. You will see how:
- One Order can contain many Order Details (line items.)
- One Product can appear in many Order Details.
- One Category can contain many products.
- One Customer can have many orders.
and so on. That's the core of how the database gets designed.

Northwind is just a sample, and does not extend to invoicing, payments
received, purchase orders, orders received, and inventory management. That
will all take considerably longer to achieve. As a starter on how you might
handle inventory, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html