kbsupplies
New member
- Local time
- Today, 22:09
- Joined
- Jun 7, 2011
- Messages
- 1
Hi everyone,
I just want to start by saying thank you to those that have helped in the past - not directly to me, but through answering others questions.
To the point: I've been running a small business from home, so I decided to create a database that would enable me to insert customer details (with the option of reusing this data to add to orders) as well as supplying an invoice (simple query specifying order number, report to show order details).
This has worked all well and good for a year or so now (as excel was freezing up anytime I enetered a new order - too many calculations on the same worksheet), however... I want more out of it.
My outcomes are as follows: customer details to be entered and applied to any exisiting or new orders, a log of how many items sold incl. costs/revenue (inventory) and the ability to create charts for monthly sales etc.
My first DB was something to this:
tbl_cust:
Cust_ID (Auto#, PK)
Name
etc
tbl_items
Item_ID (Auto#, PK)
Name
tbl_orders
Order_ID (Auto#, PK)
Cust_ID (from tbl_cust)
Item 1 (from tbl_items)
Item 1 Quant.
Item 1 Price
Item 2 (from tbl_items)
Item 2 Quant.
Item 2 Price
...
Item 6...
I had it set up in a way where a customer can buy different items, price always varies along with quantity.
This works fine for inputting data into the tables, and a report (with help of a query to determine totals) is used to print off an invoice... however after reading up, I realised that my DB is not normalised. So with the new prototype, I've got
tbl_cust - same as above
tbl_orders
Order_ID (Auto#, PK)
Cust_ID (from tbl_cust)
tbl_items - same as above
tbl_item1
Item_ID (from tbl_items)
Quant.
Price
...
through to tbl_item6
I guess my question is, is this the correct way to go about inputting data? And if so, is it normalised enough so that eventually I can create a report that spits out sales VS costs for any time frame specified?
I'm fairly new to all of this, and I appreciate anyone spending the time to read through and assist, I respect your patience! I would just like a few handy hints that I can work on.
Thanks in advance!
Kieran.
I just want to start by saying thank you to those that have helped in the past - not directly to me, but through answering others questions.
To the point: I've been running a small business from home, so I decided to create a database that would enable me to insert customer details (with the option of reusing this data to add to orders) as well as supplying an invoice (simple query specifying order number, report to show order details).
This has worked all well and good for a year or so now (as excel was freezing up anytime I enetered a new order - too many calculations on the same worksheet), however... I want more out of it.
My outcomes are as follows: customer details to be entered and applied to any exisiting or new orders, a log of how many items sold incl. costs/revenue (inventory) and the ability to create charts for monthly sales etc.
My first DB was something to this:
tbl_cust:
Cust_ID (Auto#, PK)
Name
etc
tbl_items
Item_ID (Auto#, PK)
Name
tbl_orders
Order_ID (Auto#, PK)
Cust_ID (from tbl_cust)
Item 1 (from tbl_items)
Item 1 Quant.
Item 1 Price
Item 2 (from tbl_items)
Item 2 Quant.
Item 2 Price
...
Item 6...
I had it set up in a way where a customer can buy different items, price always varies along with quantity.
This works fine for inputting data into the tables, and a report (with help of a query to determine totals) is used to print off an invoice... however after reading up, I realised that my DB is not normalised. So with the new prototype, I've got
tbl_cust - same as above
tbl_orders
Order_ID (Auto#, PK)
Cust_ID (from tbl_cust)
tbl_items - same as above
tbl_item1
Item_ID (from tbl_items)
Quant.
Price
...
through to tbl_item6
I guess my question is, is this the correct way to go about inputting data? And if so, is it normalised enough so that eventually I can create a report that spits out sales VS costs for any time frame specified?
I'm fairly new to all of this, and I appreciate anyone spending the time to read through and assist, I respect your patience! I would just like a few handy hints that I can work on.
Thanks in advance!
Kieran.
