UPDATE All Columns except one

[JensB]

Hi
I got at table with 40 Columns (Pricelist).
I am not able to use the wildcard Pricelist.*
Is there a work arround to avoid mentioning all 40 column names in the SQL
sentence

Regards
/JensB

 

[John Spencer]

No, you must list the columns.

In query design view, you can select all the fields at once and add them to
the query. Double click on the table title to select all the fields. Then
click on one of the fields and drag them all into the grid. You can then get
rid of the one field you don't want to change.

40 columns for a price list sounds wrong. I would suspect that your design
for this table is not normalized.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[JensB]

John .Thx for your answer.
Just to clarify about the pricelist issue.
The pricelist contain prices for different products/quallities in relation
to the two parameters (Speed, Width), something like this
<---- Prices -----------
Speed Width Suppl1 Supl2 Supl3....
200 1600 3000
200 1800 4500
400 1600 3900
400 1800 5500
600 1600 5100
600 1800 6400

If there is a smarter way, I would like to know.

/JensB

 

[John Spencer]

A better structure would be along the lines of a table with
Speed
Width
Supplier (Or whatever Supl1 represents)
Price

So you would end up with multiple rows for each row you now have. You could
then get the maximum price, minimum price, average price easily with a query.

You could with a little more complexity in the query return the "supplier?"
that had the maximum or minimum price.

You could even display the data in the same manner as you currently see it
using a crosstab query.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County