Updating product prices

[G.W.Green]

You can follow these instructions to increase your product prices by the amount of the tax rate increase using a SQL statement.

PLEASE NOTE:

You must follow the instructions to backup your EPOS database and ‘Checkout’ folder before proceeding.

You must not run the SQL statement more than once, as it will increase the prices by more than the TAX rate increase.

The SQL statement updates all the product prices that fall into the 'Standard' sales tax band, and all such prices are rounded to the nearest penny.

1. First make a backup of your EPOS system ( Select ‘Backup’ from the ‘Data’ menu)
2. Also make a backup of your ‘Checkout’ folder. This can usually be found at location ‘C:\Checkout’. Save a copy of it to a safe location
3. Open Windows Explorer
4. Browse to: ‘C:\Checkout’
5. To open ‘data.mdb’ in Access, right click on it and select ‘Open with’, then ‘Microsoft Access’
6. If you are using Access 2007, a security warning will appear. Click the ‘Options’ button and select ‘Enable this content’.
7. In Access go to the 'View’ menu and select ‘Database Objects’, then ‘Queries’
8. Click 'New'
9. Select 'Design' view
10. Click 'Close' on the 'Show Table' window
11. Go to 'View’, then ‘SQL view'
12. Copy and paste the SQL statement below into the page, replacing ‘SELECT;’ if that is already there:

UPDATE tblProduct INNER JOIN tblProduct_BarCode ON tblProduct.ProductCode
= tblProduct_BarCode.ProductCode SET
Region1 = Round(Region1/1175*1200,2),
Region2 = Round(Region2/1175*1200,2),
Region3 = Round(Region3/1175*1200,2),
Region4 = Round(Region4/1175*1200,2),
Region5 = Round(Region5/1175*1200,2),
Region6 = Round(Region6/1175*1200,2),
tblProduct.ListPrice = Round(tblProduct.ListPrice/1175*1200,2),
tblProduct_Barcode.ListPrice =
Round(tblProduct_Barcode.ListPrice/1175*1200,2),
Trade = Round(Trade/1175*1200,2),
WholeSale = Round(WholeSale/1175*1200,2),
tblProduct.NewPrice = Round(tblProduct.NewPrice/1175*1200,2),
tblProduct_Barcode.NewPrice =
Round(tblProduct_Barcode.NewPrice/1175*1200,2),
[Used Buy] = Round([Used Buy]/1175*1200,2),
[Used Sell] = Round([Used Sell]/1175*1200,2),
tblProduct.CashBuy = Round(tblProduct.CashBuy/1175*1200,2),
tblProduct_Barcode.CashBuy =
Round(tblProduct_Barcode.CashBuy/1175*1200,2),
OnlinePrice = Round(OnlinePrice/1175*1200,2) WHERE
tblProduct.SalesTaxCode="S"