database design

[Avir]

I have a table which has following fields

Date
OrderNumber
CustomerName
Item
Qnty
ShippedYesNo (Yes/No)


I want to keep a track of payment received from a customer. (like a debit and credit account).

Should I make another table? If yes, then after we ship some goods to a customer, how do I post the entry in accounts table? By running append query?

Could somebody please help me? I am a newbee.

 

[Armen Stein]

I have a table which has following fields

Date
OrderNumber
CustomerName
Item
Qnty
ShippedYesNo (Yes/No)


I want to keep a track of payment received from a customer. (like a debit and credit account).

Should I make another table? If yes, then after we ship some goods to a customer, how do I post the entry in accounts table? By running append query?

Could somebody please help me? I am a newbee.

Hi,

A few suggestions:

First, the table you show is not normalized. The CustomerName should be
in another table, probably tblCustomer, and should be joined using a
CustomerKey or CustomerID. If an Order can include more than one Item,
you should have an OrderHeader table that has the OrderNumber and the
Date. Also, you don't show whether your Items are in another table;
they should be.

I'm not sure where your Item pricing is coming from. I would think you
need a price in the table you show.

And yes, transactions for Customers should be in another table. There
are a variety of ways to keep track of a balance. You can post payments
as negative numbers and any miscellaneous charges or finance fees as
positive numbers. These transactions can be summed with the ordered
item prices to calculate a current balance.

Hope this helps,

--
Armen Stein
Access 2003 VBA Programmer's Reference
http://www.amazon.com/exec/obidos/ASIN/0764559036/jstreettech-20
J Street Technology, Inc.
Armen _@_ JStreetTech _._ com