UPDATE All Columns except one

Discussion in 'Microsoft Access Queries' started by JensB, May 6, 2010.

  1. JensB

    JensB Guest

    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
     
    JensB, May 6, 2010
    #1
    1. Advertisements

  2. JensB

    John Spencer Guest

    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 wrote:
    > 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, May 6, 2010
    #2
    1. Advertisements

  3. JensB

    JensB Guest

    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" <> wrote in message
    news:%...
    > 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 wrote:
    >> 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
     
    JensB, May 6, 2010
    #3
  4. JensB

    John Spencer Guest

    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

    JensB wrote:
    > 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" <> wrote in message
    > news:%...
    >> 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 wrote:
    >>> 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, May 6, 2010
    #4
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. naniboujou
    Replies:
    3
    Views:
    228
    naniboujou
    Dec 13, 2003
  2. Henro

    Code to export ALL tables except one?

    Henro, Jan 11, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    153
    Henro
    Jan 11, 2004
  3. Guest

    SELECT ALL RECORDS EXCEPT

    Guest, Mar 16, 2005, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    138
    John Vinson
    Mar 16, 2005
  4. Guest

    Remove all data from field except a part

    Guest, Apr 19, 2005, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    142
    John Spencer (MVP)
    Apr 19, 2005
  5. Guest

    Return all Fields Except Memo Fields

    Guest, Apr 19, 2006, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    157
    Guest
    Apr 19, 2006
Loading...

Share This Page