Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Re: Get a record from a previous field and insert multiple records

Discussion in 'Information Technology' started by David Lloyd, Oct 27, 2005.

  1. David Lloyd

    David Lloyd
    Expand Collapse
    Guest

    Mark:

    The following query seems to work in updating the SKU values. You can
    expand the UPDATE query to include fields other than SKU.

    UPDATE Fruit, Fruit AS Fruit_1 SET Fruit.SKU = [Fruit_1].[SKU]
    WHERE (((Fruit.SKU) Is Null) AND ((Fruit_1.ID)=(SELECT MAX(ID) FROM Fruit as
    Fruit2 WHERE Fruit2.ID<Fruit.ID)));

    --
    David Lloyd
    MCSD .NET
    http://LemingtonConsulting.com

    This response is supplied "as is" without any representations or warranties.


    "Markf" <Markf@discussions.microsoft.com> wrote in message
    news:828B28C2-5581-4F11-9432-AD8E8214AEFD@microsoft.com...
    I've just imported a bunch of records and am trying to cleanup the data.
    Here
    is an example of what the table looks like:

    ID SKU Fruit Status Location
    --------------------------------------------------------------------------
    1 922968 Apple In RF
    2 924626 Orange In JF
    3 Grape
    4 Cherry
    5 924630 Apple Out RF
    6 Grape
    7 924631 Orange Out RF
    8 925178 Banana In RF
    9 Cherry
    10 Orange
    11 Grape
    12 925203 Orange Out JF
    13 Cherry
    14 926348 Apple Out JF

    The records that have no data other than 'Fruit' are part of the previous
    record but just were outputted on a separate line. The ID field Access added
    as a key.

    Is there any way to take the previous SKU and fill down the next null SKUs
    or optimally output to a separate related Fruit table?

    Thanks!
    Mark
     
  2. Loading...


  3. Markf

    Markf
    Expand Collapse
    Guest

    Hey, that works perfectly. Thanks David!

    "David Lloyd" wrote:

    > Mark:
    >
    > The following query seems to work in updating the SKU values. You can
    > expand the UPDATE query to include fields other than SKU.
    >
    > UPDATE Fruit, Fruit AS Fruit_1 SET Fruit.SKU = [Fruit_1].[SKU]
    > WHERE (((Fruit.SKU) Is Null) AND ((Fruit_1.ID)=(SELECT MAX(ID) FROM Fruit as
    > Fruit2 WHERE Fruit2.ID<Fruit.ID)));
    >
    > --
    > David Lloyd
    > MCSD .NET
    > http://LemingtonConsulting.com
    >
    > This response is supplied "as is" without any representations or warranties.
    >
    >
    > "Markf" <Markf@discussions.microsoft.com> wrote in message
    > news:828B28C2-5581-4F11-9432-AD8E8214AEFD@microsoft.com...
    > I've just imported a bunch of records and am trying to cleanup the data.
    > Here
    > is an example of what the table looks like:
    >
    > ID SKU Fruit Status Location
    > --------------------------------------------------------------------------
    > 1 922968 Apple In RF
    > 2 924626 Orange In JF
    > 3 Grape
    > 4 Cherry
    > 5 924630 Apple Out RF
    > 6 Grape
    > 7 924631 Orange Out RF
    > 8 925178 Banana In RF
    > 9 Cherry
    > 10 Orange
    > 11 Grape
    > 12 925203 Orange Out JF
    > 13 Cherry
    > 14 926348 Apple Out JF
    >
    > The records that have no data other than 'Fruit' are part of the previous
    > record but just were outputted on a separate line. The ID field Access added
    > as a key.
    >
    > Is there any way to take the previous SKU and fill down the next null SKUs
    > or optimally output to a separate related Fruit table?
    >
    > Thanks!
    > Mark
    >
    >
    >
     
  4. Markf

    Markf
    Expand Collapse
    Guest

    Thanks again for your help David. I have another question. The table that I
    am updating has…at this point…about a 1000 rows and for the query to update
    it, it takes about 8 mins…on a Dell Optiplex SX280. Am I missing something in
    the design or would this be faster in VB? If so, would you have any idea how
    to convert the SQL to VB…very new to VB.

    Cheers,
    Mark


    "David Lloyd" wrote:

    > Mark:
    >
    > The following query seems to work in updating the SKU values. You can
    > expand the UPDATE query to include fields other than SKU.
    >
    > UPDATE Fruit, Fruit AS Fruit_1 SET Fruit.SKU = [Fruit_1].[SKU]
    > WHERE (((Fruit.SKU) Is Null) AND ((Fruit_1.ID)=(SELECT MAX(ID) FROM Fruit as
    > Fruit2 WHERE Fruit2.ID<Fruit.ID)));
    >
    > --
    > David Lloyd
    > MCSD .NET
    > http://LemingtonConsulting.com
    >
    > This response is supplied "as is" without any representations or warranties.
    >
    >
    > "Markf" <Markf@discussions.microsoft.com> wrote in message
    > news:828B28C2-5581-4F11-9432-AD8E8214AEFD@microsoft.com...
    > I've just imported a bunch of records and am trying to cleanup the data.
    > Here
    > is an example of what the table looks like:
    >
    > ID SKU Fruit Status Location
    > --------------------------------------------------------------------------
    > 1 922968 Apple In RF
    > 2 924626 Orange In JF
    > 3 Grape
    > 4 Cherry
    > 5 924630 Apple Out RF
    > 6 Grape
    > 7 924631 Orange Out RF
    > 8 925178 Banana In RF
    > 9 Cherry
    > 10 Orange
    > 11 Grape
    > 12 925203 Orange Out JF
    > 13 Cherry
    > 14 926348 Apple Out JF
    >
    > The records that have no data other than 'Fruit' are part of the previous
    > record but just were outputted on a separate line. The ID field Access added
    > as a key.
    >
    > Is there any way to take the previous SKU and fill down the next null SKUs
    > or optimally output to a separate related Fruit table?
    >
    > Thanks!
    > Mark
    >
    >
    >
     

Share This Page