Update Price Based On % From Other Table

[Auto]

hi all.
i am having a table which is called TmpTable where i store Temporary Sales.
then i have a table named Sale, where i store info on some items which has a sale, and the item is 30% off or so.
there is a field in the sale table named "Sale" its a int field, and the number stored in this field, is the percentage what should be deducted on sale.

now i created a sql statement which needs to update the price from the tmptable based on the percent found in the sale table, but somehow it dont work, here is my example.

UPDATE TempTbl
SET Price = Price - Sale.Sale / 100 * Price
WHERE Item = Sale.Item;

can anyone tell if there is another way to do it?
thanks anyone who can help.

[rpeare]

I'd recommend you create a SELECT query first to make sure your formula is actually doing what you intend, then change it to an update query.

Are both tables (TempTbl and Sale) represented in your query? are they properly linked (by the item). what are your values in your SALE field, is it a decimal value (i.e. .3 for 30%) or is it stored as text? Have you checked your bracketing? your formula:

Price - Sale.Sale / 100 * Price

I *think* will try to perform the multiplication first, division second, and the subtraction last

So if you have a 30% discount (value in the SALE field) and the item is 100$

Your formula would perform the last part of the formula first

100 * 100 = 10000

Then divide the discount by this number

.3/10000 = .00003

Then subtract the value from the price

100 - .00003 = 99.99997

If you want to apply a 30% discount to your price the formula would be

Price * Sale.Sale

Assuming that the value in the SALE field is the actual decimal value of the sale.

[Auto]

thanks for your reply.

the field has the number 30 for 30% discount, and a 3 if i want 3% discount.

the tables has an inner join on the item.

but i will try a different approach using your suggestion Price * Sale.Sale and see how it work out.

[rpeare]

if the value is being stored as a number your formula would be


Price = Price * (1 - (Sale.Sale/100))

or

Price = Price - (Price * (Sale.Sale/100))

[Auto]

ok here is my new code i try to use, but i am still getting an error for missing operators.

UPDATE TempTbl SET Price = Price - ( Price * (Sale.Sale/100))
FROM TempTbl INNER JOIN Sale ON TempTbl.ITEM = Sale.ITEM;

what is wrong?

[rpeare]

can you create a sample database and post it, just make sure to zip it before you post and only include enough data to give an example of your data.

[Auto]

ok, Here is a example 2007 database.

and thank you so much for helping me out here.

[rpeare]

Ok the problem here (at least with your example) is that neither table has a primary key defined. The only way I am aware of to create an update/updateable query is to link through the PK of the child table, in your case table SALE. That does not help you with your query. I think what I would do if you

a. Want to do this with queries
b. Want to do this with your current data structure

would be to figure the discount price BEFORE adding it to the temp table in the first place. Is that a possibility for you? Then you could create the calculated value and append it without issue.

Otherwise I don't know of a way to do this with no primary key defined. Your other option is to set the primary key of the SALE table to be a a combination of the ID and ITEM field as it looks like you're storing a history of sale dates and percentages.

So this is what I did to get a working query:

1. In the properties of the SALE table I made the ID and ITEM fields the primary key (normally you do NOT want to create a dual primary key, we're just trying to get this working right now)
2. In the TEMPTBL table I created a new field PRICE2, this is the field i'll be updating so you can keep the original value of your sale price)
3. Used this query to update TEMPTBL:

UPDATE TempTbl LEFT JOIN Sale ON TempTbl.ITEM = Sale.ITEM SET TempTbl.Price2 = [price]*(1-([sale]/100));

Now. Just a few things of note here.

1. There is really no reason to perform this update. What you are doing is storing a calculated value with no audit trail to help you get back to why the price was altered. I don't know if that's an issue for you but personally I would leave the sale price alone and calculate the discount on whatever form/report you need to.
2. Storing the text value of your item is a bad idea, what you really should be doing is storing your item's primary key (autonumbers are a great PK), You want to use a value that has no chance of being altered as your database expands. In your tables, if you change item the name of an item from XXXXXX to YYYYYYY you are going to orphan a bunch of your data, making it unusable. If you store the PK of the item you will not run this risk.
3. The query I gave you does not account for whether or not the sale of the item fell within the range of the sale, nor does it take into account the criteria of selling x amount of dollars worth of the product (or does the 'criteria' field mean your total bill has to be at least x).

[Auto]

ok, thanks again for taking your time to help me, i really appreciate that.

here is the idea what happens.

the user gets an order list which is coming original from an excel template, and its being imported in the db, so in order to keep the records with all info needed, i just import it to a temp table first, so there will be no option to figure the value before import.

then the user needs to put in on a form which customer it was, but i dont want the user should be able to apply discounts on their own, so i made a table called sales, where i store records for every sale being introduced with the dates when they will expire, and the % amount, and if there will be a criteria for the sale, for example only if users order reach $50 and up, its all being stored in the record.

now since the sale is ONLY for some items, not all of them, and since i need to store the initial amount how much it was that particular sale, i taught to do it with a docmd.runSQL methed and place the sql code in it, and executing it when user clicks ok on the form after entering the customer info.

the issue about PK, the sale table has a PK which is called ID, the temptbl dont have a PK, the reason why i didnt made the item as a PK, since its a Text field, and i am not sure if it will not hang down the db, or make it behave wiered as you stated on number 2.

the reason why i store the item not an ID of the item, since its coming from excel templates, and i have no way to have ID's in it, so i base on the ITEM number which is the only Unique identifier for each item.

hope this answers the questions.

did you think i should make the item as PK?

[rpeare]

If you want your the query I gave you to be updatable, yes, the SALE table needs to have the item number/autonumber field be it's PK otherwise this query will never be updatable. The other option you have is to use VBA to cycle through the records on the temp table and update them accordingly. I don't know how big that temp table gets or how large your sale table is but cycling through individual records is less efficient than a query but at least you wouldn't have to mess with your table structure.

[Auto]

I don't know how big that temp table gets or how large your sale table is but cycling through individual records is less efficient than a query but at least you wouldn't have to mess with your table structure.

yes, it could get like few hundered records easy, thats why i didnt went with looping through records in first place.

so i guess my best bet would be to have the ITEM number being the PK.

thanks a billion for your help, i have no words for you.
thanks, thanks, thanks.