Welcome to SPN

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

Sign Up Now!

Fill Down a Column

Discussion in 'Information Technology' started by devine, Jul 28, 2006.

Tags:
  1. devine

    devine
    Expand Collapse
    Guest

    I have a column in my table that contains blank values. Those blank values
    are related to the record directly above the blank value. I want to be able
    to use an update query to fill in the blanks. I did this once before, but I
    can't remember how I did it. I remember it involved creating a function in
    VB and then calling that function as part of my update query. The function
    basically stated " set the first record as the one to copy to the next record
    if the value of the second record is null". It looked for null values and
    filled in the null field with the field data directly above it. It worked
    beautifully. Has anyone ever had occasion to use that type of function in
    conjunction with an update query?
     
  2. Loading...

    Similar Threads Forum Date
    The Ten Methods of meditating on the world that can lead to happiness and fulfillment Interfaith Dialogues Dec 15, 2012
    Islam Habibiyya - Fanafillah Interfaith Dialogues Sep 8, 2012
    SciTech Magnetic bubbles fill edge of solar system Breaking News Jun 10, 2011
    1984 DSGMC dilly delaying for filling case against Kamal Nath, alleges Phoolka History of Sikhism May 31, 2010
    Kamdhayn/ਕਾਮਧੇਨ & Wish-fulfilling Elysian Tree. Spiritual Articles Jul 20, 2009

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 13 Jun 2006 18:04:02 -0700, devine
    <devine@discussions.microsoft.com> wrote:

    >I have a column in my table that contains blank values. Those blank values
    >are related to the record directly above the blank value.


    Ummm... No, they aren't, not in a properly normalized table.

    A table HAS NO ORDER. There *is* no such concept as "the record
    directly above".

    You can't count on the records being presented in any particular
    order, unless you use a Query sorting the records.

    John W. Vinson[MVP]
     
  4. devine

    devine
    Expand Collapse
    Guest

    Yes you can. When your first column is an autonumber as the ID and Primary
    Key with no duplicates, the records appear in ID sort order every time. My
    table didn't have a changce to be a "properly normalized table". The blank
    values are the result of an import from a txt file. I've actually done this
    before. When I figure out what I did before, I'll post it. That way you can
    put it in your own bag of tricks. Once you see what I'm talking about, your
    going to love it. And I suspect you'll use it all the time.
    ------------------------------------------------------------------------------------------------

    "John Vinson" wrote:

    > On Tue, 13 Jun 2006 18:04:02 -0700, devine
    > <devine@discussions.microsoft.com> wrote:
    >
    > >I have a column in my table that contains blank values. Those blank values
    > >are related to the record directly above the blank value.

    >
    > Ummm... No, they aren't, not in a properly normalized table.
    >
    > A table HAS NO ORDER. There *is* no such concept as "the record
    > directly above".
    >
    > You can't count on the records being presented in any particular
    > order, unless you use a Query sorting the records.
    >
    > John W. Vinson[MVP]
    >
     
  5. devine

    devine
    Expand Collapse
    Guest

    Eurika! I finally remembered what I did. I'm going to post it here, so you
    can use it, and even pass it on if you find anyone ever asks this quesion
    again... (because your response wasn't really helpful, and I thought the
    point of this comunity was to be helpful... and your tone was not helpful
    either)...

    My table looks something like this:

    Order Date PO Description Qty ExtSell ExtCost
    ExtGP
    S2281331 6/1/06 XYZ GHDIUDUUFU 2 3.00 1. 00 2.00
    CLSIDNGNDI 3 4.00 2.00
    2.00
    PDOINDNF 1 2.00 1.00
    1.00
    S2283746 6/1/06 ABC POINDNFI 2 2.00 1.00 1.00

    And so on.....

    I want to fill in the blank fields with the same field in the record
    directly above it in data sheet view (but only when it's blank)

    I created the following VB code:

    Option Compare Database
    Option Explicit
    Public HoldOrder As String
    Public HoldDate As Date
    Public HoldPO As String
    ---------------------------------------------------------
    Public Function FillOrder(ID) As String
    If IsNull(ID) Then Else HoldOrder = ID
    FillOrder = HoldOrder
    End Function
    ---------------------------------------------------------
    Public Function FillDate(ID) As Date
    If IsNull(ID) Then Else HoldDate = ID
    FillDate = HoldDate
    End Function
    ---------------------------------------------------------
    Public Function FillPO(ID) As String
    If IsNull(ID) Then Else HoldPO = ID
    FillPO = HoldPO
    End Function
    --------------------------------------------------------

    Then, in my update query, I built it this way:

    UPDATE [Sdge-open] SET [Sdge-open].[Order] = FillOrder([Order]),
    [Sdge-open].[Date] = FillDate([Date]), [Sdge-open].PO = FillPO([PO]);

    Turns out you don't actaully need there to be an "ID" column with autonumber
    etc. This VB code in conjunction with the update query works beautifully.
    It works every time. Try it. I think you'll like it.

    -----------------------------------------------------------------------------------------------

    "John Vinson" wrote:

    > On Tue, 13 Jun 2006 18:04:02 -0700, devine
    > <devine@discussions.microsoft.com> wrote:
    >
    > >I have a column in my table that contains blank values. Those blank values
    > >are related to the record directly above the blank value.

    >
    > Ummm... No, they aren't, not in a properly normalized table.
    >
    > A table HAS NO ORDER. There *is* no such concept as "the record
    > directly above".
    >
    > You can't count on the records being presented in any particular
    > order, unless you use a Query sorting the records.
    >
    > John W. Vinson[MVP]
    >
     
  6. dbahooker@hotmail.com

    dbahooker@hotmail.com
    Expand Collapse
    Guest

    you do need a primary key.

    dont ever build a single table without a pk and don't talk back to
    people that help you


    -Aaron


    devine wrote:
    > Eurika! I finally remembered what I did. I'm going to post it here, so you
    > can use it, and even pass it on if you find anyone ever asks this quesion
    > again... (because your response wasn't really helpful, and I thought the
    > point of this comunity was to be helpful... and your tone was not helpful
    > either)...
    >
    > My table looks something like this:
    >
    > Order Date PO Description Qty ExtSell ExtCost
    > ExtGP
    > S2281331 6/1/06 XYZ GHDIUDUUFU 2 3.00 1. 00 2.00
    > CLSIDNGNDI 3 4.00 2.00
    > 2.00
    > PDOINDNF 1 2.00 1.00
    > 1.00
    > S2283746 6/1/06 ABC POINDNFI 2 2.00 1.00 1.00
    >
    > And so on.....
    >
    > I want to fill in the blank fields with the same field in the record
    > directly above it in data sheet view (but only when it's blank)
    >
    > I created the following VB code:
    >
    > Option Compare Database
    > Option Explicit
    > Public HoldOrder As String
    > Public HoldDate As Date
    > Public HoldPO As String
    > ---------------------------------------------------------
    > Public Function FillOrder(ID) As String
    > If IsNull(ID) Then Else HoldOrder = ID
    > FillOrder = HoldOrder
    > End Function
    > ---------------------------------------------------------
    > Public Function FillDate(ID) As Date
    > If IsNull(ID) Then Else HoldDate = ID
    > FillDate = HoldDate
    > End Function
    > ---------------------------------------------------------
    > Public Function FillPO(ID) As String
    > If IsNull(ID) Then Else HoldPO = ID
    > FillPO = HoldPO
    > End Function
    > --------------------------------------------------------
    >
    > Then, in my update query, I built it this way:
    >
    > UPDATE [Sdge-open] SET [Sdge-open].[Order] = FillOrder([Order]),
    > [Sdge-open].[Date] = FillDate([Date]), [Sdge-open].PO = FillPO([PO]);
    >
    > Turns out you don't actaully need there to be an "ID" column with autonumber
    > etc. This VB code in conjunction with the update query works beautifully.
    > It works every time. Try it. I think you'll like it.
    >
    > -----------------------------------------------------------------------------------------------
    >
    > "John Vinson" wrote:
    >
    > > On Tue, 13 Jun 2006 18:04:02 -0700, devine
    > > <devine@discussions.microsoft.com> wrote:
    > >
    > > >I have a column in my table that contains blank values. Those blank values
    > > >are related to the record directly above the blank value.

    > >
    > > Ummm... No, they aren't, not in a properly normalized table.
    > >
    > > A table HAS NO ORDER. There *is* no such concept as "the record
    > > directly above".
    > >
    > > You can't count on the records being presented in any particular
    > > order, unless you use a Query sorting the records.
    > >
    > > John W. Vinson[MVP]
    > >
     

Share This Page