473,320 Members | 1,933 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Adding records to a table based on the value of one field of other

My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUMBER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTION")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sql)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.

Mar 9 '07 #1
4 1761
I'm not even sure what valid purpose would be served by doing so. From your
example, it seems to be the work of the Department of Redundancy
Department -- that is, creating a number of redundant records. But, then,
there might be a purpose in "real life" when you aren't obscuring it with
"sample data".

Larry Linson
Microsoft Access MVP
<ca******@gmail.comwrote in message
news:11**********************@v33g2000cwv.googlegr oups.com...
My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUMBER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTION")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sql)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.

Mar 10 '07 #2
On 10 mar, 06:28, "Larry Linson" <boun...@localhost.notwrote:
I'm not even sure what valid purpose would be served by doing so. From your
example, it seems to be the work of the Department of Redundancy
Department -- that is, creating a number of redundant records. But, then,
there might be a purpose in "real life" when you aren't obscuring it with
"sample data".

Larry Linson
Microsoft Access MVP
Your answer was very helpful,
now I realize why you are MVP ;)

Of course there is a "real life" purpose, however it is not the main
issue in this thread (and forum).
Thank you again.

Mar 14 '07 #3
ca******@gmail.com wrote in news:1173469137.480177.256350
@v33g2000cwv.googlegroups.com:
My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUMBER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTION")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sql)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.
I think the fastest way is likely to be to create a DAO recordset of
Table2 records and to use AddNew and Update in keeping with the values
found in Table1. In my experience, this is a case in which DAO is not
just fast, but blows the sox off ADO and/or JET SQL. If there are a bunch
of new records to create I'd guess this way might be ten or more times
faster than the fastest other way.
BTW, I use ADO wherever DAO or SQL does not have a clear advantage; in
this case I think the advantage is overwhelming.

--
Mar 14 '07 #4
Thank you lyle

Mar 15 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Jamie Fryatt | last post by:
Hi everyone, here's what id like to do. I have a table with 2 fields, name and value I need to be able to add multiple records quickly, for example I need to add name value abc 1...
3
by: Jason | last post by:
I am trying to filter records in a primary form based on records in related tables. The data in the related tables is being displayed in the primary form through subforms. To be more specific, I...
2
by: Robin S. | last post by:
This is an "Add product" form. The user will enter a ProductNo (catalog number), select a Product Class (from cascading combo boxes) and then click a button to create the product. When a...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
6
by: 6thirty | last post by:
Hi, I've created a stocktaking database using Access XP. This is indexed by two fields - part number and shelf location. I am currently inputting all the data via a form. When I have entered a...
3
by: Jim Heavey | last post by:
Trying to figure out the technique which should be used to add rows to a datagrid. I am thinking that I would want an "Add" button on the footer, but I am not quite sure how to do that. Is that...
6
by: Rudy | last post by:
Hi all, I know this is easy, just can't seem to get it. I have a windows form, and a text box, with a value already in it. I need to add that value to a table. It's just one value, so the entire...
16
by: Ian Davies | last post by:
Hello Needing help with a suitable solution. I have extracted records into a table under three columns 'category', 'comment' and share (the category column also holds the index no of the record...
1
damonreid
by: damonreid | last post by:
Access 2003 Microsoft Windows XP Pro Hey, I am currently pulling my hair out here. I have a Form for adding new projects to a database, the only problem is that when I close the form it doesn't...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.