We want to change the commission properties for items in our database, with different commission rate for different as below: Men?s Clothing ? 2% Women?s Clothing ? 2% Men?s Accessories ? 1% Men?s Accessories ? 1% And this will be a percent of sale. Is this done with an update query?
It can be done with an SQL update query.
How do you know if an item is men's acc... ?
Hi Davidjc52 , here are some sample queries you can use.
Connect to your database using SO Administrator. First you need to know your department ID assignments, so run
select * from department (confirm and jot down the ID assigned to mens clothing, mens accessories, womens clothing and womens accessories) As an example, let's assume the following are the IDs assigned to these depts. mens clothing = 3 mens accessories = 4 womens clothing = 7 womens accessories = 8
its usually a good idea to run some select queries first to make sure all the items you will affect with your update are the ones you intend to affect, so try
select itemlookupcode, description, price from item where departmentid = 3 (the above would show you the ILC, description and price of all items in mens clothing) - run the same select query replacing the "3" with 4, 7 and
8 to see items in the other departments.If all the items in your select query should be affected by your update query, you are ready to update, BACKUP YOUR DATABASE FIRST AND RUN AFTER HOURS IF POSSIBLE.
Your update query for mens and womens clothing to pay 2% commission is (replace "X" with your mens clothing department ID and "Y" with your womens clothing department ID) update item set commissionmode = 2, commissionpercentsale = 2 where departmentid = X or departmentid = Y
Your update query for mens and womens accessories to pay 1% commission is (replace "A" with your mens accessories department ID and "B" with your womens accessories department ID) update item set commissionmode = 2, commissionpercentsale = 1 where departmentid = A or departmentid = B
** REMEMBER TO BACKUP BEFORE RUNNING THE UPDATE QUERIES IN CASE YOU MAKE A TYPING MISTAKE ON THE UPDATE SYNTAX **H> No those are each departments.
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.