Microsoft Office Forums


Reply
Thread Tools Display Modes

Using VBA to Loop through Access Table

 
 
David McCulloch
Guest
Posts: n/a
 
      05-11-2006, 01:06 AM
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


 
Reply With Quote
 
 
 
 
Ken Snell \(MVP\)
Guest
Posts: n/a
 
      05-11-2006, 02:04 AM
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$(E-Mail Removed)...
>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
>



 
Reply With Quote
 
 
 
 
David McCulloch
Guest
Posts: n/a
 
      05-11-2006, 10:09 AM
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)" <(E-Mail Removed)> wrote in message
news:u6%(E-Mail Removed)...
>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$(E-Mail Removed)...
>>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
>>

>
>



 
Reply With Quote
 
Ken Snell \(MVP\)
Guest
Posts: n/a
 
      05-11-2006, 01:16 PM
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$(E-Mail Removed)...
> 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)" <(E-Mail Removed)> wrote in message
> news:u6%(E-Mail Removed)...
>>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$(E-Mail Removed)...
>>>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
>>>

>>
>>

>
>



 
Reply With Quote
 
David McCulloch
Guest
Posts: n/a
 
      05-11-2006, 09:24 PM
Ken,
Thanks very much -- that helps a lot!

Dave

"Ken Snell (MVP)" <(E-Mail Removed)> wrote in message
news:OLA%(E-Mail Removed)...
> 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$(E-Mail Removed)...
>> 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)" <(E-Mail Removed)> wrote in message
>> news:u6%(E-Mail Removed)...
>>>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$(E-Mail Removed)...
>>>>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
>>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Access VBA - Loop through table fields -> update Listbox Cam Access VBA Modules 1 11-27-2008 01:15 PM
Help with VBA/SQL to loop through Table eliminating offsets EagleOne@microsoftdiscussiongroups Access General 4 08-20-2008 10:44 PM
loop through field names, then use to loop through records topopulate grid pmacdiddie@gmail.com Access Forms Coding 1 01-04-2008 01:13 PM
loop through columns (using for-loop) Kurt Excel Programming 2 11-23-2007 08:56 PM
Access 2000 - Loop through a Memo field with VBA Tony_VBACoder Access VBA Modules 5 04-05-2006 03:57 PM


All times are GMT. The time now is 08:15 AM.
Microsoft Office Forums is not affiliated with Microsoft Corporation.


Welcome!
Welcome to the Microsoft Office Forums
 


Latest Threads
Mail Merging Content With High Quality
oleander (04-17-2014, 11:59 PM)

Aargh. What is this?
PeteJ (04-17-2014, 12:10 PM)

Word 2013 Insert Symbol (Not Responding)
tpthrshr (04-17-2014, 02:49 AM)

Can't save a large Word document
Josh (04-17-2014, 01:03 AM)

Remote help?
newman200 (04-13-2014, 12:54 PM)