Software

Ask something
Discussion Locked

Access: Update query with new records

0 helpful
5 replies

by alanevs

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I know how to write an update query toggling binary criteria, but how can I update non-uniform price increases across a product line without losing other data, such as power consumed (which lives in the same table)
Perhaps pricing should be in a related table, instead. Still, I should be able to perform this common task.

5 replies

by roc180

Your question is a litle confusing but I think your asking how to update multiple records with different data. For example:
Prod Line 1 Item A Price 2.00
Prod Line 1 Item B Price 2.50

And you want to update the price to be
If Price=2.00THEN ($1.00+Price)
Else(Price + .50)

If so use:
UPDATE Tbl Set price=IIF(Price=2,(price+1),(price+.5)) WHERE Prod Line=1

Again if this is not what your looking for drop me a line with a little more info and I can work up an example for you.
Neal Martinelli

by donq

Roc180's answers are fine. I wanted to adress your "where does price belong" concern.

I assume you have a "Products" Table with a "Cost" and/or "Price" (currency) field?
Both Price and Cost are facts that belong to a Product, and therefore Price belongs right where it is and an Update query is the correct way of changing each records stored value even if your expression must refer to a [second field name] within the expression.

This discussion is closed to new replies.