Update problem

  • Thread starter Thread starter Jens Burup
  • Start date Start date
J

Jens Burup

Hi Ng
I have been through many issues in this group but did not find something to
help me, so please...

I have a table (Price list) with 20 columns and 80 rows, and wants to update
these all prices with the same factor.
This is not a problem for me, but....
some of cells contain either an 0 or 1 for which I use as a flag.
I don't want the 1 to be updated (Zero are not a problem -:))

This is how I did:
UPDATE Mtable
SET Mtable, Mtable.Price1 = [Mtable].[price1]*1.7..........Mtable,
Mtable.Price20 = [Mtable].[price20]*1.7
WHERE
Mtable.Price1>1 and ........ Mtable.Price20>1 I also tryed with OR

When I use this SQL String no updates takes place.
When I remove the WHERE, clause all prices are updated, but my 1 (flag)
became a 2
How do I avoid values below 2 not to be updated?
Any help would be appreciated

Regards
Jens Burup
 
Jens Burup said:
Hi Ng
I have been through many issues in this group but did not find something to
help me, so please...

I have a table (Price list) with 20 columns and 80 rows, and wants to update
these all prices with the same factor.
This is not a problem for me, but....
some of cells contain either an 0 or 1 for which I use as a flag.
I don't want the 1 to be updated (Zero are not a problem -:))

This is how I did:
UPDATE Mtable
SET Mtable, Mtable.Price1 = [Mtable].[price1]*1.7..........Mtable,
Mtable.Price20 = [Mtable].[price20]*1.7
WHERE
Mtable.Price1>1 and ........ Mtable.Price20>1 I also tryed with OR

When I use this SQL String no updates takes place.
When I remove the WHERE, clause all prices are updated, but my 1 (flag)
became a 2
How do I avoid values below 2 not to be updated?
Any help would be appreciated

Regards
Jens Burup
Click to expand...

Jens,

You are having problems because the tabel is not normailzed. Instead of
"Exel spreadsheet layout (rows and columns), I would have fields:

PriceID - autonumber
txtItem - item name
curPrice - currency
intSequence - a number from 1 to 20 (Price 1, Price2,...)

then your query would work using: UPDATE...WHERE curPrice>1

If you can't change the structure then try this:
(you will have to expand this to the 20 columns)

UPDATE mtable SET mtable.price1 = IIf([price1]>1,1.7,1)*[price1],
mtable.price2 = IIf([price2]>1,1.7,1)*[price2], mtable.price3 =
IIf([price3]>1,1.7,1)*[price3], mtable.price4 =
IIf([price4]>1,1.7,1)*[price4], mtable.price5 =
IIf([price5]>1,1.7,1)*[price5];


If you will be updating the prices more than this one time, it would be
easier to use a form to get the update factor from.

So, create a form and add an unbound text control. Open the properties form
for the control. On the FORMAT tab, set Format to 'Standard'. I called the
control 'PriceChg'

Add a button to run the query. I named the button 'btnDoIt'.
Paste the following code in the OnClick event:

'---------------------------------------
Private Sub btnDoIt_Click()
On Error GoTo Err_btnDoIt_Click

Dim stDocName As String

stDocName = "mprice2"
'DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName
'DoCmd.SetWarnings True

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

End Sub
'---------------------------------------

Change stDocName = "mprice2" to the name of your update query.

Uncomment the DoCmd.SetWarnings if you don't want the warnings when you run
the query.

Change the query to:

UPDATE mtable SET mtable.price1 =
IIf([price1]>1,[forms]![form1].[PriceChg],1)*[price1], mtable.price2 =
IIf([price2]>1,[forms]![form1].[PriceChg],1)*[price2], mtable.price3 =
IIf([price3]>1,[forms]![form1].[PriceChg],1)*[price3], mtable.price4 =
IIf([price4]>1,[forms]![form1].[PriceChg],1)*[price4], mtable.price5 =
IIf([price5]>1,[forms]![form1].[PriceChg],1)*[price5];


All that is left to do is change [form1] and [PriceChg] to the names you
use for the form and the text control on the form.

I did test this, but ....... <grin> I've got a looooooong way to go to even
see a MVP,...........

HTH
Steve
 
You are having problems because the tabel is not normailzed. Instead of
"Exel spreadsheet layout (rows and columns), I would have fields:

PriceID - autonumber
txtItem - item name
curPrice - currency
intSequence - a number from 1 to 20 (Price 1, Price2,...)

then your query would work using: UPDATE...WHERE curPrice>1

If you can't change the structure then try this:
(you will have to expand this to the 20 columns)

UPDATE mtable SET mtable.price1 = IIf([price1]>1,1.7,1)*[price1],
mtable.price2 = IIf([price2]>1,1.7,1)*[price2], mtable.price3 =
IIf([price3]>1,1.7,1)*[price3], mtable.price4 =
IIf([price4]>1,1.7,1)*[price4], mtable.price5 =
IIf([price5]>1,1.7,1)*[price5];


If you will be updating the prices more than this one time, it would be
easier to use a form to get the update factor from.

So, create a form and add an unbound text control. Open the properties form
for the control. On the FORMAT tab, set Format to 'Standard'. I called the
control 'PriceChg'

Add a button to run the query. I named the button 'btnDoIt'.
Paste the following code in the OnClick event:

'---------------------------------------
Private Sub btnDoIt_Click()
On Error GoTo Err_btnDoIt_Click

Dim stDocName As String

stDocName = "mprice2"
'DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName
'DoCmd.SetWarnings True

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

End Sub
'---------------------------------------

Change stDocName = "mprice2" to the name of your update query.

Uncomment the DoCmd.SetWarnings if you don't want the warnings when you run
the query.

Change the query to:

UPDATE mtable SET mtable.price1 =
IIf([price1]>1,[forms]![form1].[PriceChg],1)*[price1], mtable.price2 =
IIf([price2]>1,[forms]![form1].[PriceChg],1)*[price2], mtable.price3 =
IIf([price3]>1,[forms]![form1].[PriceChg],1)*[price3], mtable.price4 =
IIf([price4]>1,[forms]![form1].[PriceChg],1)*[price4], mtable.price5 =
IIf([price5]>1,[forms]![form1].[PriceChg],1)*[price5];


All that is left to do is change [form1] and [PriceChg] to the names you
use for the form and the text control on the form.

I did test this, but ....... <grin> I've got a looooooong way to go to even
see a MVP,...........

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

sanfu at techie dot com
Click to expand...


Tanks Steve, this is great.

I was not aware that an IIF statement could be used in SQL.!( I am getting
out of shape-:))

I use my Access Database as back-end for a very large VS vb.net project,
where there are many tables with the same problematic as above.

Each row contain beside the prices, a lot of other columns with application
data for a specific machine.

This means I can't change the design of the data structure.

I just wanted to create a small ACCESS tool for the administrator to changes
prices in these tables.

I did use a textbox as multiplier factor function as you described, so this
works well.



Anyway you gave me the key to solve a problem, and thanks for that.



Regards

Jens
 
Jens Burup said:
You are having problems because the tabel is not normailzed. Instead of
"Exel spreadsheet layout (rows and columns), I would have fields:

PriceID - autonumber
txtItem - item name
curPrice - currency
intSequence - a number from 1 to 20 (Price 1, Price2,...)

then your query would work using: UPDATE...WHERE curPrice>1

If you can't change the structure then try this:
(you will have to expand this to the 20 columns)

UPDATE mtable SET mtable.price1 = IIf([price1]>1,1.7,1)*[price1],
mtable.price2 = IIf([price2]>1,1.7,1)*[price2], mtable.price3 =
IIf([price3]>1,1.7,1)*[price3], mtable.price4 =
IIf([price4]>1,1.7,1)*[price4], mtable.price5 =
IIf([price5]>1,1.7,1)*[price5];


If you will be updating the prices more than this one time, it would be
easier to use a form to get the update factor from.

So, create a form and add an unbound text control. Open the properties form
for the control. On the FORMAT tab, set Format to 'Standard'. I called the
control 'PriceChg'

Add a button to run the query. I named the button 'btnDoIt'.
Paste the following code in the OnClick event:

'---------------------------------------
Private Sub btnDoIt_Click()
On Error GoTo Err_btnDoIt_Click

Dim stDocName As String

stDocName = "mprice2"
'DoCmd.SetWarnings False
DoCmd.OpenQuery stDocName
'DoCmd.SetWarnings True

Exit_btnDoIt_Click:
Exit Sub

Err_btnDoIt_Click:
MsgBox Err.Description
Resume Exit_btnDoIt_Click

End Sub
'---------------------------------------

Change stDocName = "mprice2" to the name of your update query.

Uncomment the DoCmd.SetWarnings if you don't want the warnings when you run
the query.

Change the query to:

UPDATE mtable SET mtable.price1 =
IIf([price1]>1,[forms]![form1].[PriceChg],1)*[price1], mtable.price2 =
IIf([price2]>1,[forms]![form1].[PriceChg],1)*[price2], mtable.price3 =
IIf([price3]>1,[forms]![form1].[PriceChg],1)*[price3], mtable.price4 =
IIf([price4]>1,[forms]![form1].[PriceChg],1)*[price4], mtable.price5 =
IIf([price5]>1,[forms]![form1].[PriceChg],1)*[price5];


All that is left to do is change [form1] and [PriceChg] to the names you
use for the form and the text control on the form.

I did test this, but ....... <grin> I've got a looooooong way to go to even
see a MVP,...........

HTH
Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

sanfu at techie dot com
Click to expand...


Tanks Steve, this is great.

I was not aware that an IIF statement could be used in SQL.!( I am getting
out of shape-:))

I use my Access Database as back-end for a very large VS vb.net project,
where there are many tables with the same problematic as above.

Each row contain beside the prices, a lot of other columns with application
data for a specific machine.

This means I can't change the design of the data structure.

I just wanted to create a small ACCESS tool for the administrator to changes
prices in these tables.

I did use a textbox as multiplier factor function as you described, so this
works well.



Anyway you gave me the key to solve a problem, and thanks for that.



Regards

Jens
Click to expand...

Jens,

You're welcome

Now I understand why all of the columns.

Just be sure after you type a value in the text box you press Enter or move
out of the box - otherwise the text box value is seen as NULL and all of the
prices except 0 & 1 will be NULL - *not* what you want!!!

Steve
 
Jens,
You're welcome

Now I understand why all of the columns.

Just be sure after you type a value in the text box you press Enter or move
out of the box - otherwise the text box value is seen as NULL and all of the
prices except 0 & 1 will be NULL - *not* what you want!!!

Steve
Click to expand...

Hi Steve

Yes I added some logic to the interface (an update button), securing the
value in the textbox to be a double, so everything works fine now.

But another issue occurred during my test, as some of the columns are
negative (a reduction in the price)

So I changed the code from this:

IIf([price1]>1,1.7,1)*[price1] ) '' This code changes my 0 to 1 which I
didn't want.

to this:

IIf([price1]<>1,1.7,[price1]*[price1]) '' This will keep 0 and 1 as
earlier

regards
Jens
 
Back
Top