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. 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.
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. 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.
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
| |