INSERT INTO help please...

[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

[salad]

jamesfreddyc wrote:

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

Go to the queries tab, click new, Crosstab (or Pivot) and play around
with that.

You might need to create a routine/function to insert the results since
grouped queries usually don't work in append queries.

[Kc-Mass]

Create your new table and run
something of this nature (Air code).

Sub Unstack()
Dim db As Database
Dim RSOld As Recordset
Dim RSNew As Recordset
Dim strTrapMark As String
Dim strtDateMark as Date
Set db = CurrentDb
Set RSOld = db.OpenRecordset("qry1", dbOpenSnapshot)
Set RSNew = db.OpenRecordset("tblNew", dbOpenDynaset)
RSOld.MoveFirst
Do While Not RSOld.EOF
strTrapMark = RSOld!TrapNo
strtDateMark = RSOld!tDate
RSNew.AddNew
RSNew!Tdate = RSOld!TDate
RSNEW!TrapNo= RSOLd!Trapno
Do While RSOld!Tdate = strDateMark AND _
RSOld!TrapNo = strTrapMark And Not RSOld.EOF
If RSOld!Species = "TAE" then
RSNew!TAE = RSOld!AmtColl
ElseIf RSOld!Species = "COL" Then
RSNew!COL = RSOld!AmtColl
Else
RSNew!Species = RSOld!AmtColl
End If
RSNew.Update
RSOld.MoveNext
Loop
Loop

Kevin C

"jamesfreddyc" <ja**********@comcast.netwrote in message
news:11*********************@k70g2000cwa.googlegro ups.com...

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

[breadon]

Try sql along the following lines:

select a.tDate, a.TrapNo, a.amtcoll as TAE, b.amtcoll as COL, cmtcoll
as TIT
from myTable a, myTable b, myTable c
where a.tDate = b.tDate
and a.tDate = c.TDate
and a.TrapNo = b.TrapNo
and a.TrapNo = c.TrapNo
and a.Species = "TAE"
and b.Species = "COL"
and c.Species = "TIT"