This is for a church where CD's of the sermons are sold, and some people pay in advance, and I need a system that tracks sales and the credit balance of those who pay in advance.

 

I have 2 tables -

 

1 x Customers

1 x sales

 

The customer table has an "Amount" field - this is the credit amount paid (say $60.00) - not all customers have a credit amount.

 

The Sales table has a drop down menu with "Payment Status" options are "Credit", "Paid" , "To Pay"

 

The Sales table also has an amount - as some purchase more than 1 CD per week (CD cost = $1.00).

 

I would like it to credit the "Amount" field of $1.00 for each CD sold to a customer (2 x cd = 2.00 etc) - so the updated credit balance is displayed (I have a Portal of sales records for each customer)

 

I have everything working except I have a mind blank on how to do this one simple calculation.

 

(Database Attached)

You may want to search around for a basic invoicing demo file. Normally there is a line items table but it seems as though in your case there wouldnt be more than 1 line per sale. You can choose to use 1 single transactions table of sales / payments or you can use a related payments table. I wouldnt store any amount in the customer table.

Thank you for this info - but just one thing, what problems will storing the balance in the customer table cause?

Because if you have orders and payments, the balance for an account should be automatically calculated, not manually entered.