Question (1 Viewer)
- Thread starter Nicolette
- Start date
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?
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.
Just have selling price as a field in the tblOrderDetails. Make default Zero to keep Null values out of the system.
You enter the value when you record a sale and this is then stored as the sale price for that record.
No need to have a field on tblInventory.
Your "profit on sale" can be shown with unbound calculated fields on the sales form or another form should you wish. Don't store this, just view it when you so wish.
You enter the value when you record a sale and this is then stored as the sale price for that record.
No need to have a field on tblInventory.
Your "profit on sale" can be shown with unbound calculated fields on the sales form or another form should you wish. Don't store this, just view it when you so wish.
This method would also require a new table to hold the bid records (tblBidRecords) and this would include a field with OrderHeaderID.
I don't know how the system for internet bids works and how you would capture these. maybe stage two
At this point I have no need to keep record of the bids and I don't really think I will ever have a need fo that in this database simply because my current auction software basically does that
You can do this with a calculated unbound text box control in your form or as you are storing this value in tblOrderDetails then include it in the query for your orders form.
You will need to either include the state sales tax value (zero for most) in the query or alternativly, use DLookup("[filedname]", "tblename") to get the sales tax value.
The query field is simple. SalesTax: [SalePrice]*[SatateSalesTax]
I will try and do an sql and post.
This sql will give you the sales tax for each item in your orders.
Some notes:
I may have prev advised to use 1.125 in tblStates where sales tax is 12.5%.
As you may want to calculate the actual sales tax then change this to .125 as 1.125 will give you the result after adding sales tax where as .125 will give the sales tax.
Change the fields in tblCustomers for Billing and delivery States to long integer. You had text and that won't allow a join for the query.
Of course the sql isn't the full answer, just shows how to calculate. You will need to incorporate this into your sql in the form.
Make sure you enter a sales tax for all states - zero where no tax.
PHP:
SELECT tblOrderHeader.OrderID, tblOrderDetails.OrderDetailPK, tblOrderDetails.SKU, tblOrderDetails.OrderQty, tblOrderDetails.OrderPrice, ([OrderPrice]*[OrderQty])*[StateTax] AS OrderTax
FROM ((tblCustomers LEFT JOIN tblStates ON tblCustomers.BillingState = tblStates.StateID) RIGHT JOIN tblOrderHeader ON tblCustomers.CustomerID = tblOrderHeader.CustomerID) INNER JOIN tblOrderDetails ON tblOrderHeader.OrderID = tblOrderDetails.OrderID;I may have prev advised to use 1.125 in tblStates where sales tax is 12.5%.
As you may want to calculate the actual sales tax then change this to .125 as 1.125 will give you the result after adding sales tax where as .125 will give the sales tax.
Change the fields in tblCustomers for Billing and delivery States to long integer. You had text and that won't allow a join for the query.
Of course the sql isn't the full answer, just shows how to calculate. You will need to incorporate this into your sql in the form.
Make sure you enter a sales tax for all states - zero where no tax.
What form do want to show it on. I don't have a form on the sample - I think?
Try this as the sql for frmCreateInvoiceSUB
PHP:
SELECT tblOrderDetails.OrderDetailPK, tblOrderDetails.OrderID, tblOrderDetails.SKU, tblOrderDetails.OrderQty, tblInventory.ProductTitle, tblStates.StateTax, tblOrderDetails.OrderPrice, [OrderPrice]*[OrderQty]*[StateTax] AS OrderTax
FROM (tblCustomers INNER JOIN (tblOrderHeader INNER JOIN (tblInventory INNER JOIN tblOrderDetails ON tblInventory.SKU = tblOrderDetails.SKU) ON tblOrderHeader.OrderID = tblOrderDetails.OrderID) ON tblCustomers.CustomerID = tblOrderHeader.CustomerID) INNER JOIN tblStates ON tblCustomers.BillingState = tblStates.StateID;You need to bring into the sql the tblCustomers and tblStates so you can get the StateTax for the customer. This is done on the sql above.
You then need to add the StateTax and OrderTax fields (text box controls) to the form.
You then need to add the StateTax and OrderTax fields (text box controls) to the form.
