If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help Designing a database
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 a 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 |
#2
|
|||
|
|||
Help Designing a database
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 "1foxi" wrote in message ... 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 a 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 |
#3
|
|||
|
|||
Help Designing a database
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 "1foxi" wrote in message ... 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 a 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 |
Thread Tools | |
Display Modes | |
|
|