INSERT INTO HELP

  • Thread starter Thread starter jamesfreddyc
  • Start date Start date
J

jamesfreddyc

Query1:
tDate TrapNo Species AmtColl
1/1/2000 E1 TAE 100
1/1/2000 E1 COL 150
1/1/2000 E1 TIT 205

I need to Insert these rows into another table with this setup:
tDate TrapNo TAE COL TIT
1/1/2000 E1 100 150 205


Any suggestions are greatly appreciated!


Take Care,


j
 
hi,

The basic syntax for a query which makes a table is:

SELECT tbl_old.column1, tbl_old.column2, tbl_old.column3, tbl_old.column4
INTO tbl_new
FROM tbl_old;


hope this helps,
geebee
 
geebee said:
hi,

The basic syntax for a query which makes a table is:

SELECT tbl_old.column1, tbl_old.column2, tbl_old.column3, tbl_old.column4
INTO tbl_new
FROM tbl_old;


hope this helps,
geebee
Click to expand...

1). "tbl_new" is alread setup:
tDate TrapNo TAE COL TIT

2). "orig_qry" is setup like this:
tDate TrapNo Species AmtColl

So, I am getting an error from the mismatched query values and field
numbers. I am having difficulty laying out the INSERT INTO query in a
logical fashion. What I need is to insert the AmtColl value into the
correct "species" field.

j
 
hi,

If the 2 columns are not the same data type, you may get an error message.
It doesn't matter whether or not the new table has already been created,
because it will be overwritten anyway. Have you considered an append query?
The basic syntax for such is:

INSERT INTO tbl_new ( column1, column2 )
SELECT tbl_old.column1, tbl_old.column2
FROM tbl_old;
 
geebee said:
hi,

If the 2 columns are not the same data type, you may get an error message.
It doesn't matter whether or not the new table has already been created,
because it will be overwritten anyway. Have you considered an append query?
The basic syntax for such is:

INSERT INTO tbl_new ( column1, column2 )
SELECT tbl_old.column1, tbl_old.column2
FROM tbl_old;
Click to expand...

Thanks for the reply...

Well, that's my problem... I have two seperate tables with different
field names (but the same field types). Basically, I need to reformat
the original table ---

Original Setup:
tDate TrapNo Species AmtColl
1/1/2000 E1 TAE 100
1/1/2000 E1 COL 150
1/1/2000 E1 TIT 205

The new setup basically spreads out the "species" column into 3 new
columns ("TAE, COL, TIT), where the values for those colums are found
in the "AmtColl" field from the original. Like this:

tDate TrapNo TAE COL TIT
1/1/2000 E1 100 150 205


Thanks for your help!

j
 
You could do that this way.

INSERT INTO tbl_new ( tDate , TrapNo, TAE , COL, TIT)
SELECT tDate
, TrapNo
, IIF(Species="TAE",AmtCol,Null)
, IIF(Species="COL",AmtCol,Null)
, IIF(Species="TTT",AmtCol,Null)
FROM orig_qry;


Also, you could try using a cross-tab query as the source query where you
use
tDate and tTrapNo as rows
Species as Column Heading
and Max(AmtCol) as the Value
 
Your will have to use VB code to step through your first table and write the
records into the second. However, your example is too small to give a
complete answer. What should the output look like with the following table:
tDate TrapNo Species AmtColl
1/1/2000 E1 TAE 100
1/1/2000 E1 COL 150
1/1/2000 E1 TIT 205
1/2/2000 E1 TAE 100
1/2/2000 E1 COL 150
1/2/2000 E1 TIT 205
1/1/2000 E2 TAE 100
1/1/2000 E2 COL 150
1/1/2000 E2 TIT 205
1/2/2000 E3 TAE 100
1/2/2000 E3 TAE 150
1/2/2000 E3 TIT 205

In otherwords, how do you group the date and Trap Number?

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger said:
Your will have to use VB code to step through your first table and write the
records into the second. However, your example is too small to give a
complete answer. What should the output look like with the following table:
tDate TrapNo Species AmtColl
1/1/2000 E1 TAE 100
1/1/2000 E1 COL 150
1/1/2000 E1 TIT 205
1/2/2000 E1 TAE 100
1/2/2000 E1 COL 150
1/2/2000 E1 TIT 205
1/1/2000 E2 TAE 100
1/1/2000 E2 COL 150
1/1/2000 E2 TIT 205
1/2/2000 E3 TAE 100
1/2/2000 E3 TAE 150
1/2/2000 E3 TIT 205

In otherwords, how do you group the date and Trap Number?
Click to expand...


The Output should look like:

tDate TrapNo TAE COL TIT
1/1/2000 E1 100 150 205
1/1/2000 E2 100 150 205
1/2/2000 E1 100 150 205
1/2/2000 E3 100 150 205
 
OK, assuming your original table is called: "Query1" (it can actually be a
query for that matter) and your target table is called "NewTable", you can
use the following subroutine:

'-------------------
Sub FixTable()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb
Set rsSource = db.OpenRecordset("Query1", dbOpenDynaset)
Set rsTarget = db.OpenRecordset("NewTable", dbOpenDynaset)
Dim strCriteria As String

Do While Not rsSource.EOF
strCriteria = "[tDate] = #" & rsSource!tdate & "# And [TrapNo] = '"
& rsSource!trapno & "'"
rsTarget.FindFirst strCriteria
If Not rsTarget.NoMatch Then
rsTarget.Edit
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
Else
rsTarget.AddNew
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
End If

rsSource.MoveNext
Loop
rsSource.Close
rsTarget.Close
db.Close
End Sub
'------------------

Put this function in a general module, put the cursor anywhere in the module
and click the Run button.
NOTE: This routine requires a Reference set to Microsoft DAO Object Library
3.6 under Tools > Reference in any code window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger said:
OK, assuming your original table is called: "Query1" (it can actually be a
query for that matter) and your target table is called "NewTable", you can
use the following subroutine:

'-------------------
Sub FixTable()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb
Set rsSource = db.OpenRecordset("Query1", dbOpenDynaset)
Set rsTarget = db.OpenRecordset("NewTable", dbOpenDynaset)
Dim strCriteria As String

Do While Not rsSource.EOF
strCriteria = "[tDate] = #" & rsSource!tdate & "# And [TrapNo] = '"
& rsSource!trapno & "'"
rsTarget.FindFirst strCriteria
If Not rsTarget.NoMatch Then
rsTarget.Edit
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
Else
rsTarget.AddNew
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
End If

rsSource.MoveNext
Loop
rsSource.Close
rsTarget.Close
db.Close
End Sub
'------------------

Put this function in a general module, put the cursor anywhere in the module
and click the Run button.
NOTE: This routine requires a Reference set to Microsoft DAO Object Library
3.6 under Tools > Reference in any code window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

Thanks -- I'll give it a go.

j
 
Roger said:
OK, assuming your original table is called: "Query1" (it can actually be a
query for that matter) and your target table is called "NewTable", you can
use the following subroutine:

'-------------------
Sub FixTable()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb
Set rsSource = db.OpenRecordset("Query1", dbOpenDynaset)
Set rsTarget = db.OpenRecordset("NewTable", dbOpenDynaset)
Dim strCriteria As String

Do While Not rsSource.EOF
strCriteria = "[tDate] = #" & rsSource!tdate & "# And [TrapNo] = '"
& rsSource!trapno & "'"
rsTarget.FindFirst strCriteria
If Not rsTarget.NoMatch Then
rsTarget.Edit
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
Else
rsTarget.AddNew
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
End If

rsSource.MoveNext
Loop
rsSource.Close
rsTarget.Close
db.Close
End Sub
'------------------

Put this function in a general module, put the cursor anywhere in the module
and click the Run button.
NOTE: This routine requires a Reference set to Microsoft DAO Object Library
3.6 under Tools > Reference in any code window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

Roger -- your example works great. I certainly appreciate the help!

Take Care,

j
 
You're welcome.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


jamesfreddyc said:
Roger said:
OK, assuming your original table is called: "Query1" (it can actually be a
query for that matter) and your target table is called "NewTable", you can
use the following subroutine:

'-------------------
Sub FixTable()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Set db = CurrentDb
Set rsSource = db.OpenRecordset("Query1", dbOpenDynaset)
Set rsTarget = db.OpenRecordset("NewTable", dbOpenDynaset)
Dim strCriteria As String

Do While Not rsSource.EOF
strCriteria = "[tDate] = #" & rsSource!tdate & "# And [TrapNo] = '"
& rsSource!trapno & "'"
rsTarget.FindFirst strCriteria
If Not rsTarget.NoMatch Then
rsTarget.Edit
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
Else
rsTarget.AddNew
rsTarget!tdate = rsSource!tdate
rsTarget!trapno = rsSource!trapno
rsTarget(rsSource!species) = rsSource!amtcoll
rsTarget.Update
End If

rsSource.MoveNext
Loop
rsSource.Close
rsTarget.Close
db.Close
End Sub
'------------------

Put this function in a general module, put the cursor anywhere in the module
and click the Run button.
NOTE: This routine requires a Reference set to Microsoft DAO Object Library
3.6 under Tools > Reference in any code window.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
Click to expand...

Roger -- your example works great. I certainly appreciate the help!

Take Care,

j
Click to expand...
 
Query1:
tDate TrapNo Species AmtColl
1/1/2000 E1 TAE 100
1/1/2000 E1 COL 150
1/1/2000 E1 TIT 205

I need to Insert these rows into another table with this setup:
tDate TrapNo TAE COL TIT
1/1/2000 E1 100 150 205


Any suggestions are greatly appreciated!
Click to expand...

A crosstab query will create the one row from the 3, and then you
can base the INSERT on the crosstab.
 
Back
Top