Help Designing a database

[Guest]

Hi,

I want to have a database to record all of my departments spending. I
started to design my database but I keep coming up with problems so wondered
if anyone could help.

The tables I have created already are:

Suppliers - All the suppliers we use - Supplier ID, Supplier Name, Contact
Details
Products - All the products we buy - Product ID, Product Name, Cost
Orders - the orders we make - Order Id, Date Ordered, Date Paid, Product ID,
Supplier ID

I want to create relationships so I can have a form which shows the orders I
have made and the products that have made up that order.

Can anyone help make me deisng this and do the right relationships?

Cheers

 

[Jeff Boyce]

If you are tracking spending, have you considered using software already
available off the shelf for that (e.g., accounting, bookkeeping, ...)?

Have you looked at the Northwind (sample) database that comes with Access?

Have you looked at "templates" Microsoft offers?

Have you searched on-line for existing models/systems?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve]

Suppliers ---
If there is only one contact, what you have is good. If there are more than
one contact, you need a separate contact table that looks like:
TblContact
ContactID
SupplierID
<<Contact Details>>

Products ---
Costs change over time so if you want to track changes in cost, you need a
separate cost table that looks like:
TblProductCost
ProductCostID
ProductID
ProductCostDate
ProductCost
In ProductCostDate, you record the "As Of Date" when a product cost changes.

Orders ---
You need two tables here because an order will most times have more than one
line item:
TblOrder
OrderID
OrderDate
SupplierID

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price
When entering line items in TblOrderDetail, you get the Price from
TblProductCost. Enter the actual dollars and cents and not ProductCostID to
make it easy to do reports on orders, Don't create an ExtendedPrice field;
it is a calculated value and as such can be calculated when you need it on
forms and reports.

Payments ----
I put payments in a separate table because typically there are multiple
payments on an order due to payment plans, backorders, etc. If you ALWAYS
make one payment on an order, you can put DatePaid in TblOrder. If that is
the case, the assumption is that you paid the full amount calculated as the
sum of extended prices from TblOrderDetail.
TblPayment
PaymentID
OrderID
PaymentDate
PaymentAmount
<<Other details as needed such as CheckNum)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)