Update Query - How To?

[Big_Si]

Hi

I built a quotation system which has worked fine for my purposes over the last year. I now need to update all the pricing against all the products in my product table. I have a complete list of the new product prices but am struggling to update my original table using an update query. The prices haven't all increased by a uniform percentage (if that makes any difference)

I assumed it would be quite easy using the update query option but this doesn't seem to work the way I need it too. I suspect I am missing the method behind it an would appreciate if anybody can point me in the right direction

Many Thanks

 

[dkinley]

The problem I see is the bit about "The prices haven't all increased by a uniform percentage." If this were true, in the update query you could do a [FieldName] *.02 in the Update row for that field and you would be done with it.

This introduces the second problem which is how do you select only those that are updated uniformly? Are they contained within a specific range of primary key numbers?

In putting together update queries, I normally do the following. I create a standard select query and add only the fields that are necessary to selecting the records I want manipulated. Then I add the field (or fields) I want to update. I then switch this to an update query (which will add the update row much like a totals row). In this row, I place in what I want to update - it can be text, numbers, formula, etc. Make sure you only run it once if it is a formula.

As a precaution, make a backup copy of the table since there is no undo. I also like to randomly select records to verify the update was accurate and successful. I usually switch back to a select style of query and compare the updated field with a copy of the original field (so I can see the original data).

This link has some sample update queries: http://www.techonthenet.com/access/index.php

Hope that made sense and hope it helps ,
-dK

 

[Big_Si]

Thank for the help DK!!!

I was trying to update the pricing on all the records using values stored in a linked excel sheet. Initially it wouldn't work using the method suggested. Something about the expression not being able to update values in an unupdateable field.

i imported the excel sheet instead then it was pretty straight forward.

Thanks again.

 

[dkinley]

You're welcome! Ha - didn't mention it was an Excel spreadsheet you were trying to update. I would have not even attempted that one.

Glad you got it sorted.

-dK

 

[Brianwarnock]

Hi dK, I think that you have it wrong, it wasa db that he was updating, my gripe if I were you, is that he didn't say that he had the updates in a spreadsheet and therefore effectively in a table, completely altering the question.

Brian