PNGBill:
I was using the sample database you posted to total out the orders and I'm trying to figure out how to do the sales tax... I only charge sales tax on products sold to New Mexico residents How can I calculate the sales tax if I need to charge it?
Add a field to tblStates, say StateTax.
This will be a Number, Single and, say 2 decimal places.
All States except New Mexico will have zero (0) entered and New Mexico will have 1.1 (10%) 1.125 (12.5%) - whatever the tax is.
How do you want to handle the Tax Collection?? by Product or by Order. Order seems better. Just depends how you want to show your Invoice to your customer. Tax on the sale price of each item or on the base of the invoice.
2nd thoughts I think it is best to handle this as an item issue for future record keeping.
Add OrderTax field to tblOrderdetails. Make it Currency.
You also will need to address the issue of Selling Price.
You have OrderPrice in tblOrderDetails but this should also be in tblInventory.
Either add ProductSell (insert under productCost) or ProductMU (markup)
If you use ProductMU then you will always Calculate your sell price as required and markup can vary as you will or have all products the same.
As your business grows you may be glad of the flexibility of different markup's or different sell prices.
You will need to edit the Forms to accommodate the Tax and Selling Price.
Tax is a new issue. Selling price is already there but it is linked to ProductCost, just edit the link.
How to handle the calculation...
In your Form/Order or Invoice you can add an unbound Text Box Control and you can get this to calculate the Sales Tax. it will need to be stored in the tblOrderDetails (preserve history in case tax rate changes).
I haven't got to this yet on your copy. Sorry, been sidetracked on some issues here but this won't be hard.
The Form will handle all Totals so no need to accommodate these in your queries. Total will always be the same provided you store the Qty, Price and Tax.
Trust the above makes sence.