Using VBA to append Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I Have the following Tables:
Table A with Columns: Port(text), Sub(double),Spec(Double)
Table B with one column: PERCENT(integer)
Table C with one column: PERCENT(integer)
Table D with Columns: Port(text), Sub(double), Spec(Double)

In table A Port has values: "Egg", "Cheese", "Bacon"
Table B PERCENT has 100 values ranging from 1 to 100
Table C sam as table B
Table D is where I want to append my Calculated Values.

What i want to do something like this or similar code:

For Each Port In Table A
For Each PERCENT In Table B
For Each PERCENT In Table C
SQLString = "Insert Into Table D (Sub,Spec) & _
" Select Sub*PERCENT as A, Spec*PERCENT as B & _
" From Table A"
Docmd.RunSQL SQLString
Next
Next
Next

Where the end result is a table with 30,000 rows of data. any thoughts on
how to go about doing this. Thanks
 
Jim said:
I Have the following Tables:
Table A with Columns: Port(text), Sub(double),Spec(Double)
Table B with one column: PERCENT(integer)
Table C with one column: PERCENT(integer)
Table D with Columns: Port(text), Sub(double), Spec(Double)

In table A Port has values: "Egg", "Cheese", "Bacon"
Table B PERCENT has 100 values ranging from 1 to 100
Table C sam as table B
Table D is where I want to append my Calculated Values.

What i want to do something like this or similar code:

For Each Port In Table A
For Each PERCENT In Table B
For Each PERCENT In Table C
SQLString = "Insert Into Table D (Sub,Spec) & _
" Select Sub*PERCENT as A, Spec*PERCENT as B & _
" From Table A"
Docmd.RunSQL SQLString
Next
Next
Next

Where the end result is a table with 30,000 rows of data. any
thoughts on how to go about doing this. Thanks
Click to expand...

If I have interpreted the intent behind your pseudo-SQL statement
correctly, I think you can use this single statement, or something very
like it:

CurrentDb.Execute
"INSERT INTO D (Port, Sub, Spec) " & _
"SELECT A.Port, A.Sub * B.PERCENT, A.Spec * B.PERCENT " & _
"FROM A, B, C"

By including all three tables -- A, B, and C -- in the query without
joining them, we get a result set that has every combination of the
records in A, the records in B, and the the records in C.
 
The SQL statement ins't the Key part of the code I want to focus on, more
rather the Code to loop through rows of different tables to calculate
information and then have it dumped into a table of my choice. Any thoughts
of the General syntax on how to accomplish that?
 
Jim said:
The SQL statement ins't the Key part of the code I want to focus on,
more rather the Code to loop through rows of different tables to
calculate information and then have it dumped into a table of my
choice. Any thoughts of the General syntax on how to accomplish that?
Click to expand...

You may (or may not) have missed my point. Executing the query I
suggested eliminates all need to loop through tables -- executed once,
it will create all 30,000 records, and it will do so in a minuscule
fraction of the time it would take to open recordsets on the three
source tables and loop through them in nested loops.

Yes, I do know how to "loop through the records in a table", and I'll
tell you if you want. But it is seldom an efficient way to operate in a
relational database.
 
Dirk,
I appreciate you taking time to work with me, the reason I want to use
code versus linking the tables, In the long run, I want to use other criteria
within the code, like comparing custom variables to the data with in the
tables and then deciding that if it meets certain test do something different
with the data. That is why I want to understand the syntax for refering to
tables and inputing data into tables.
 
Jim said:
Dirk,
I appreciate you taking time to work with me, the reason I want
to use code versus linking the tables, In the long run, I want to use
other criteria within the code, like comparing custom variables to
the data with in the tables and then deciding that if it meets
certain test do something different with the data. That is why I want
to understand the syntax for refering to tables and inputing data
into tables.
Click to expand...

Okay, here's the gist of it:

' requires a reference to the Microsoft DAO Object Library

Dim db AS DAO.Database
Dim rsA As DAO.Recordset
Dim rsB AS DAO.Recordset
Dim rsC AS DAO.Recordset
Dim rsD AS DAO.Recordset

Set db = CurrentDb
Set rsA = db.OpenRecordset("TableA")
Set rsB = db.OpenRecordset("TableB")
Set rsC = db.OpenRecordset("TableC")
Set rsD = db.OpenRecordset("TableD")

Do Until rsA.EOF

If rsB.RecordCount > 0 Then rsB.MoveFirst

Do Until rsB.EOF

If rsC.RecordCount > 0 Then rsC.MoveFirst

Do Until rsC.EOF
With rsD
.AddNew
!Port = rsA!Port
!Sub = rsA!Sub * rsB!PERCENT
!Spec = rsA!Spec * rsC!PERCENT
.Update
End With
rsC.MoveNext
Loop

rsB.MoveNext

Loop

rsA.MoveNext

Loop

rsA.Close
rsB.Close
rsC.Close
rsD.Close

Set rsA = Nothing
Set rsB = Nothing
Set rsC = Nothing
Set rsD = Nothing
Set db = Nothing
 
Back
Top