Using VBA to Loop through Access Table

Discussion in 'Access VBA Modules' started by David McCulloch, May 11, 2006.

  1. I am relatively new to Access VBA coding and need some help.

    QUESTION:
    Using Access 2003 and VBA, how can I loop through a table and bring some of
    a row's columns into VB variables?

    PSEUDO CODE:
    Open table
    Do Until EOF
    var1 = ...
    var2 = ...
    < use the variables >
    Advance to next row in table
    Loop
    Close

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

    OPTIONAL READING:
    Why do I care? The short story is that I am trying to map data from an
    unnormalized Access table (i.e., one that was imported from an Excel
    spreadsheet) to a normalized Access table. To do that, I want to perform an
    update query (formatted in VBA) for each row of a column-mapping table
    ....and to start my journey, I must learn how to read my mapping table.
    Perhaps I have been looking in all the wrong places, but I could not find
    any sample code on the web.

    MORE OPTIONAL READING:
    What's the long story? It all starts with a spreadsheet that is imported
    into Access. Abstracted, its format is (with column headers):

    Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
    Part1, $1000, $2000, $1500, ...
    Part2, $5000, $5500, $6500, ...
    Part3, $3000, $2500, $4000, ...
    etc...

    There are about 1,000 Parts and many Vendors that are subject to change.

    I want to create a normalized table from the above data:

    Part, Vendor, Price
    Part1, "Vendor1", $1000
    Part1, "Vendor2", $2000
    Part1, "Vendor3", $1500
    Part2, "Vendor1", $5000
    Part2, "Vendor2", $5500
    Part2, "Vendor3", $6500
    Part3, "Vendor1", $3000
    Part3, "Vendor2", $2500
    Part3, "Vendor3", $4000
    etc...

    I could create a table of Vendors and generate a query that would give me
    all combinations of Parts and Vendors. Unfortunately, how would I set
    Price?

    I already have a Vendor table and a Parts table from which I create a
    normalized table of all Parts/Vendor combinations (similar to above, but
    with an empty Price column). From VBA, I plan to loop through the Vendors
    table and dynamically create a SQL statement to join the normalized and
    unnormalized tables (by Part) and to update the normalized table's Prices,
    vendor by vendor. I can't do that in one query, because a query can't
    dynamically reference a column with a row-dependant name (for example, the
    query's Price formula would have to reference "Vendor#_Price", but the
    Vendor is row-specific).

    Thanks very much!

    Dave
     
    David McCulloch, May 11, 2006
    #1
    1. Advertising

  2. I wouldn't use VBA code to normalize data. Instead, use a UNION query to
    normalize the data:

    SELECT T1.Part, T1.Vendor1_Price
    FROM TableName AS T1
    UNION ALL
    SELECT T2.Part, T2.Vendor2_Price
    FROM TableName AS T2
    UNION ALL
    SELECT T3.Part, T3.Vendor3_Price
    FROM TableName AS T3
    UNION ALL
    SELECT T4.Part, T4.Vendor4_Price
    FROM TableName AS T4;

    You can add more subqueries to the above if you have more than 4 prices.

    Then create an append query that uses the above query (save it) as the
    source table; the append query is then used to append the data to your new
    table.
    --

    Ken Snell
    <MS ACCESS MVP>


    "David McCulloch" <|@|.|> wrote in message
    news:d6w8g.4067$-kc.rr.com...
    >I am relatively new to Access VBA coding and need some help.
    >
    > QUESTION:
    > Using Access 2003 and VBA, how can I loop through a table and bring some
    > of a row's columns into VB variables?
    >
    > PSEUDO CODE:
    > Open table
    > Do Until EOF
    > var1 = ...
    > var2 = ...
    > < use the variables >
    > Advance to next row in table
    > Loop
    > Close
    >
    > - - - - - - - - - - - - - - - - - - -
    >
    > OPTIONAL READING:
    > Why do I care? The short story is that I am trying to map data from an
    > unnormalized Access table (i.e., one that was imported from an Excel
    > spreadsheet) to a normalized Access table. To do that, I want to perform
    > an update query (formatted in VBA) for each row of a column-mapping table
    > ...and to start my journey, I must learn how to read my mapping table.
    > Perhaps I have been looking in all the wrong places, but I could not find
    > any sample code on the web.
    >
    > MORE OPTIONAL READING:
    > What's the long story? It all starts with a spreadsheet that is imported
    > into Access. Abstracted, its format is (with column headers):
    >
    > Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
    > Part1, $1000, $2000, $1500, ...
    > Part2, $5000, $5500, $6500, ...
    > Part3, $3000, $2500, $4000, ...
    > etc...
    >
    > There are about 1,000 Parts and many Vendors that are subject to change.
    >
    > I want to create a normalized table from the above data:
    >
    > Part, Vendor, Price
    > Part1, "Vendor1", $1000
    > Part1, "Vendor2", $2000
    > Part1, "Vendor3", $1500
    > Part2, "Vendor1", $5000
    > Part2, "Vendor2", $5500
    > Part2, "Vendor3", $6500
    > Part3, "Vendor1", $3000
    > Part3, "Vendor2", $2500
    > Part3, "Vendor3", $4000
    > etc...
    >
    > I could create a table of Vendors and generate a query that would give me
    > all combinations of Parts and Vendors. Unfortunately, how would I set
    > Price?
    >
    > I already have a Vendor table and a Parts table from which I create a
    > normalized table of all Parts/Vendor combinations (similar to above, but
    > with an empty Price column). From VBA, I plan to loop through the Vendors
    > table and dynamically create a SQL statement to join the normalized and
    > unnormalized tables (by Part) and to update the normalized table's Prices,
    > vendor by vendor. I can't do that in one query, because a query can't
    > dynamically reference a column with a row-dependant name (for example, the
    > query's Price formula would have to reference "Vendor#_Price", but the
    > Vendor is row-specific).
    >
    > Thanks very much!
    >
    > Dave
    >
     
    Ken Snell \(MVP\), May 11, 2006
    #2
    1. Advertising

  3. Ken,
    VBA is the only way to automate the process so a non-programmer could run
    the application without frequent maintenance. Specifically, a UNION query
    would not accommodate [frequent] changes (i.e., new vendors, etc.) to the
    unnormalized data. I was planning to use VBA to dynamically construct a
    UNION query, but to do that, VBA would need data from other tables.

    Any idea how I could find sample code for reading rows from a table?

    Dave

    "Ken Snell (MVP)" <> wrote in message
    news:u6%...
    >I wouldn't use VBA code to normalize data. Instead, use a UNION query to
    >normalize the data:
    >
    > SELECT T1.Part, T1.Vendor1_Price
    > FROM TableName AS T1
    > UNION ALL
    > SELECT T2.Part, T2.Vendor2_Price
    > FROM TableName AS T2
    > UNION ALL
    > SELECT T3.Part, T3.Vendor3_Price
    > FROM TableName AS T3
    > UNION ALL
    > SELECT T4.Part, T4.Vendor4_Price
    > FROM TableName AS T4;
    >
    > You can add more subqueries to the above if you have more than 4 prices.
    >
    > Then create an append query that uses the above query (save it) as the
    > source table; the append query is then used to append the data to your new
    > table.
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    >
    > "David McCulloch" <|@|.|> wrote in message
    > news:d6w8g.4067$-kc.rr.com...
    >>I am relatively new to Access VBA coding and need some help.
    >>
    >> QUESTION:
    >> Using Access 2003 and VBA, how can I loop through a table and bring some
    >> of a row's columns into VB variables?
    >>
    >> PSEUDO CODE:
    >> Open table
    >> Do Until EOF
    >> var1 = ...
    >> var2 = ...
    >> < use the variables >
    >> Advance to next row in table
    >> Loop
    >> Close
    >>
    >> - - - - - - - - - - - - - - - - - - -
    >>
    >> OPTIONAL READING:
    >> Why do I care? The short story is that I am trying to map data from an
    >> unnormalized Access table (i.e., one that was imported from an Excel
    >> spreadsheet) to a normalized Access table. To do that, I want to perform
    >> an update query (formatted in VBA) for each row of a column-mapping table
    >> ...and to start my journey, I must learn how to read my mapping table.
    >> Perhaps I have been looking in all the wrong places, but I could not find
    >> any sample code on the web.
    >>
    >> MORE OPTIONAL READING:
    >> What's the long story? It all starts with a spreadsheet that is imported
    >> into Access. Abstracted, its format is (with column headers):
    >>
    >> Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
    >> Part1, $1000, $2000, $1500, ...
    >> Part2, $5000, $5500, $6500, ...
    >> Part3, $3000, $2500, $4000, ...
    >> etc...
    >>
    >> There are about 1,000 Parts and many Vendors that are subject to change.
    >>
    >> I want to create a normalized table from the above data:
    >>
    >> Part, Vendor, Price
    >> Part1, "Vendor1", $1000
    >> Part1, "Vendor2", $2000
    >> Part1, "Vendor3", $1500
    >> Part2, "Vendor1", $5000
    >> Part2, "Vendor2", $5500
    >> Part2, "Vendor3", $6500
    >> Part3, "Vendor1", $3000
    >> Part3, "Vendor2", $2500
    >> Part3, "Vendor3", $4000
    >> etc...
    >>
    >> I could create a table of Vendors and generate a query that would give me
    >> all combinations of Parts and Vendors. Unfortunately, how would I set
    >> Price?
    >>
    >> I already have a Vendor table and a Parts table from which I create a
    >> normalized table of all Parts/Vendor combinations (similar to above, but
    >> with an empty Price column). From VBA, I plan to loop through the
    >> Vendors table and dynamically create a SQL statement to join the
    >> normalized and unnormalized tables (by Part) and to update the normalized
    >> table's Prices, vendor by vendor. I can't do that in one query, because
    >> a query can't dynamically reference a column with a row-dependant name
    >> (for example, the query's Price formula would have to reference
    >> "Vendor#_Price", but the Vendor is row-specific).
    >>
    >> Thanks very much!
    >>
    >> Dave
    >>

    >
    >
     
    David McCulloch, May 11, 2006
    #3
  4. Looping through a recordset is done this way:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("SQL statement")
    If rst.EOF = False And rst.BOF = False Then
    rst.MoveFirst
    Do While rst.EOF = False
    '
    ' put code here to do something with record
    '
    rst.MoveNext
    Loop
    End If
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    --

    Ken Snell
    <MS ACCESS MVP>

    "David McCulloch" <|@|.|> wrote in message
    news:q3E8g.4084$-kc.rr.com...
    > Ken,
    > VBA is the only way to automate the process so a non-programmer could run
    > the application without frequent maintenance. Specifically, a UNION query
    > would not accommodate [frequent] changes (i.e., new vendors, etc.) to the
    > unnormalized data. I was planning to use VBA to dynamically construct a
    > UNION query, but to do that, VBA would need data from other tables.
    >
    > Any idea how I could find sample code for reading rows from a table?
    >
    > Dave
    >
    > "Ken Snell (MVP)" <> wrote in message
    > news:u6%...
    >>I wouldn't use VBA code to normalize data. Instead, use a UNION query to
    >>normalize the data:
    >>
    >> SELECT T1.Part, T1.Vendor1_Price
    >> FROM TableName AS T1
    >> UNION ALL
    >> SELECT T2.Part, T2.Vendor2_Price
    >> FROM TableName AS T2
    >> UNION ALL
    >> SELECT T3.Part, T3.Vendor3_Price
    >> FROM TableName AS T3
    >> UNION ALL
    >> SELECT T4.Part, T4.Vendor4_Price
    >> FROM TableName AS T4;
    >>
    >> You can add more subqueries to the above if you have more than 4 prices.
    >>
    >> Then create an append query that uses the above query (save it) as the
    >> source table; the append query is then used to append the data to your
    >> new table.
    >> --
    >>
    >> Ken Snell
    >> <MS ACCESS MVP>
    >>
    >>
    >> "David McCulloch" <|@|.|> wrote in message
    >> news:d6w8g.4067$-kc.rr.com...
    >>>I am relatively new to Access VBA coding and need some help.
    >>>
    >>> QUESTION:
    >>> Using Access 2003 and VBA, how can I loop through a table and bring some
    >>> of a row's columns into VB variables?
    >>>
    >>> PSEUDO CODE:
    >>> Open table
    >>> Do Until EOF
    >>> var1 = ...
    >>> var2 = ...
    >>> < use the variables >
    >>> Advance to next row in table
    >>> Loop
    >>> Close
    >>>
    >>> - - - - - - - - - - - - - - - - - - -
    >>>
    >>> OPTIONAL READING:
    >>> Why do I care? The short story is that I am trying to map data from an
    >>> unnormalized Access table (i.e., one that was imported from an Excel
    >>> spreadsheet) to a normalized Access table. To do that, I want to
    >>> perform an update query (formatted in VBA) for each row of a
    >>> column-mapping table ...and to start my journey, I must learn how to
    >>> read my mapping table. Perhaps I have been looking in all the wrong
    >>> places, but I could not find any sample code on the web.
    >>>
    >>> MORE OPTIONAL READING:
    >>> What's the long story? It all starts with a spreadsheet that is
    >>> imported into Access. Abstracted, its format is (with column headers):
    >>>
    >>> Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
    >>> Part1, $1000, $2000, $1500, ...
    >>> Part2, $5000, $5500, $6500, ...
    >>> Part3, $3000, $2500, $4000, ...
    >>> etc...
    >>>
    >>> There are about 1,000 Parts and many Vendors that are subject to change.
    >>>
    >>> I want to create a normalized table from the above data:
    >>>
    >>> Part, Vendor, Price
    >>> Part1, "Vendor1", $1000
    >>> Part1, "Vendor2", $2000
    >>> Part1, "Vendor3", $1500
    >>> Part2, "Vendor1", $5000
    >>> Part2, "Vendor2", $5500
    >>> Part2, "Vendor3", $6500
    >>> Part3, "Vendor1", $3000
    >>> Part3, "Vendor2", $2500
    >>> Part3, "Vendor3", $4000
    >>> etc...
    >>>
    >>> I could create a table of Vendors and generate a query that would give
    >>> me all combinations of Parts and Vendors. Unfortunately, how would I
    >>> set Price?
    >>>
    >>> I already have a Vendor table and a Parts table from which I create a
    >>> normalized table of all Parts/Vendor combinations (similar to above, but
    >>> with an empty Price column). From VBA, I plan to loop through the
    >>> Vendors table and dynamically create a SQL statement to join the
    >>> normalized and unnormalized tables (by Part) and to update the
    >>> normalized table's Prices, vendor by vendor. I can't do that in one
    >>> query, because a query can't dynamically reference a column with a
    >>> row-dependant name (for example, the query's Price formula would have to
    >>> reference "Vendor#_Price", but the Vendor is row-specific).
    >>>
    >>> Thanks very much!
    >>>
    >>> Dave
    >>>

    >>
    >>

    >
    >
     
    Ken Snell \(MVP\), May 11, 2006
    #4
  5. Ken,
    Thanks very much -- that helps a lot!

    Dave

    "Ken Snell (MVP)" <> wrote in message
    news:OLA%...
    > Looping through a recordset is done this way:
    >
    > Dim dbs As DAO.Database
    > Dim rst As DAO.Recordset
    > Set dbs = CurrentDb
    > Set rst = dbs.OpenRecordset("SQL statement")
    > If rst.EOF = False And rst.BOF = False Then
    > rst.MoveFirst
    > Do While rst.EOF = False
    > '
    > ' put code here to do something with record
    > '
    > rst.MoveNext
    > Loop
    > End If
    > rst.Close
    > Set rst = Nothing
    > dbs.Close
    > Set dbs = Nothing
    >
    > --
    >
    > Ken Snell
    > <MS ACCESS MVP>
    >
    > "David McCulloch" <|@|.|> wrote in message
    > news:q3E8g.4084$-kc.rr.com...
    >> Ken,
    >> VBA is the only way to automate the process so a non-programmer could run
    >> the application without frequent maintenance. Specifically, a UNION
    >> query would not accommodate [frequent] changes (i.e., new vendors, etc.)
    >> to the unnormalized data. I was planning to use VBA to dynamically
    >> construct a UNION query, but to do that, VBA would need data from other
    >> tables.
    >>
    >> Any idea how I could find sample code for reading rows from a table?
    >>
    >> Dave
    >>
    >> "Ken Snell (MVP)" <> wrote in message
    >> news:u6%...
    >>>I wouldn't use VBA code to normalize data. Instead, use a UNION query to
    >>>normalize the data:
    >>>
    >>> SELECT T1.Part, T1.Vendor1_Price
    >>> FROM TableName AS T1
    >>> UNION ALL
    >>> SELECT T2.Part, T2.Vendor2_Price
    >>> FROM TableName AS T2
    >>> UNION ALL
    >>> SELECT T3.Part, T3.Vendor3_Price
    >>> FROM TableName AS T3
    >>> UNION ALL
    >>> SELECT T4.Part, T4.Vendor4_Price
    >>> FROM TableName AS T4;
    >>>
    >>> You can add more subqueries to the above if you have more than 4 prices.
    >>>
    >>> Then create an append query that uses the above query (save it) as the
    >>> source table; the append query is then used to append the data to your
    >>> new table.
    >>> --
    >>>
    >>> Ken Snell
    >>> <MS ACCESS MVP>
    >>>
    >>>
    >>> "David McCulloch" <|@|.|> wrote in message
    >>> news:d6w8g.4067$-kc.rr.com...
    >>>>I am relatively new to Access VBA coding and need some help.
    >>>>
    >>>> QUESTION:
    >>>> Using Access 2003 and VBA, how can I loop through a table and bring
    >>>> some of a row's columns into VB variables?
    >>>>
    >>>> PSEUDO CODE:
    >>>> Open table
    >>>> Do Until EOF
    >>>> var1 = ...
    >>>> var2 = ...
    >>>> < use the variables >
    >>>> Advance to next row in table
    >>>> Loop
    >>>> Close
    >>>>
    >>>> - - - - - - - - - - - - - - - - - - -
    >>>>
    >>>> OPTIONAL READING:
    >>>> Why do I care? The short story is that I am trying to map data from an
    >>>> unnormalized Access table (i.e., one that was imported from an Excel
    >>>> spreadsheet) to a normalized Access table. To do that, I want to
    >>>> perform an update query (formatted in VBA) for each row of a
    >>>> column-mapping table ...and to start my journey, I must learn how to
    >>>> read my mapping table. Perhaps I have been looking in all the wrong
    >>>> places, but I could not find any sample code on the web.
    >>>>
    >>>> MORE OPTIONAL READING:
    >>>> What's the long story? It all starts with a spreadsheet that is
    >>>> imported into Access. Abstracted, its format is (with column headers):
    >>>>
    >>>> Part, Vendor1_Price, Vendor2_Price, Vendor3_Price, ...
    >>>> Part1, $1000, $2000, $1500, ...
    >>>> Part2, $5000, $5500, $6500, ...
    >>>> Part3, $3000, $2500, $4000, ...
    >>>> etc...
    >>>>
    >>>> There are about 1,000 Parts and many Vendors that are subject to
    >>>> change.
    >>>>
    >>>> I want to create a normalized table from the above data:
    >>>>
    >>>> Part, Vendor, Price
    >>>> Part1, "Vendor1", $1000
    >>>> Part1, "Vendor2", $2000
    >>>> Part1, "Vendor3", $1500
    >>>> Part2, "Vendor1", $5000
    >>>> Part2, "Vendor2", $5500
    >>>> Part2, "Vendor3", $6500
    >>>> Part3, "Vendor1", $3000
    >>>> Part3, "Vendor2", $2500
    >>>> Part3, "Vendor3", $4000
    >>>> etc...
    >>>>
    >>>> I could create a table of Vendors and generate a query that would give
    >>>> me all combinations of Parts and Vendors. Unfortunately, how would I
    >>>> set Price?
    >>>>
    >>>> I already have a Vendor table and a Parts table from which I create a
    >>>> normalized table of all Parts/Vendor combinations (similar to above,
    >>>> but with an empty Price column). From VBA, I plan to loop through the
    >>>> Vendors table and dynamically create a SQL statement to join the
    >>>> normalized and unnormalized tables (by Part) and to update the
    >>>> normalized table's Prices, vendor by vendor. I can't do that in one
    >>>> query, because a query can't dynamically reference a column with a
    >>>> row-dependant name (for example, the query's Price formula would have
    >>>> to reference "Vendor#_Price", but the Vendor is row-specific).
    >>>>
    >>>> Thanks very much!
    >>>>
    >>>> Dave
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
    David McCulloch, May 11, 2006
    #5
    1. Advertising

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. EagleOne@microsoftdiscussiongroups

    Help with VBA/SQL to loop through Table eliminating offsets

    EagleOne@microsoftdiscussiongroups, Aug 19, 2008, in forum: Access General
    Replies:
    4
    Views:
    56
  2. Replies:
    1
    Views:
    99
    Douglas J. Steele
    Jan 4, 2008
  3. Tony_VBACoder

    Access 2000 - Loop through a Memo field with VBA

    Tony_VBACoder, Apr 4, 2006, in forum: Access VBA Modules
    Replies:
    5
    Views:
    114
    Dirk Goldgar
    Apr 5, 2006
  4. Cam
    Replies:
    1
    Views:
    102
    Douglas J. Steele
    Nov 27, 2008
  5. Kurt

    loop through columns (using for-loop)

    Kurt, Nov 23, 2007, in forum: Excel Programming
    Replies:
    2
    Views:
    76
    JLGWhiz
    Nov 23, 2007
Loading...

Share This Page