How to copy fields values from a crosstab query and put it in a table?

[Jawad77]

I have a cross tab query whose output looks like this:

Product Location 11/5/2004 11/12/2004 11/19/2004

1 1 20 10 30

2 1 100 40 50

I like to copy these values from my cross tab query and put them in a table with the following fields like this:

Product Location LW WK2 WK3

1 1 30 10 20

2 1 50 40 100


In other words, I like to copy the values from the last field of my cross tab query output but put them in the first field of my table.

I have a code which works but it places the last feid values of cross tab query into the last field of the table rather than puttiing it in the first field of the table. Here is what my code looks like:

Public Function DeltaWorks()

Dim ObjMyDB As Database
Dim Objrst As Recordset
Dim Objrst2 As Recordset

DoCmd.SetWarnings False


Set ObjMyDB = DBEngine.Workspaces(0).Databases(0)

Set Objrst = ObjMyDB.OpenRecordset("subqry_deltaComp", DB_OPEN_DYNASET)
Set Objrst2 = ObjMyDB.OpenRecordset("IActualsTable", DB_OPEN_DYNASET)





Dim i As Integer
Dim J As Integer
i = Objrst.Fields.Count
i = i - 1


Objrst.MoveFirst



With Objrst2

Do Until Objrst.EOF

.AddNew

For J = 0 To i

.Fields(J) = Objrst.Fields(J)

Next J

.Update


Objrst.MoveNext

Loop

End With


Objrst.Close
Objrst2.Close

Set Objrst = Nothing
Set Objrst2 = Nothing

End Function




Where subqry_deltacomp is my crosstab query and IActuals is my table that I like to get values copied into


Do you have any ideas how to accomplish this. Thanks a lot in advance

Jay

 

[PHV]

Something like this ?
i = Objrst.Fields.Count - 1
Objrst.MoveFirst
With Objrst2
Do Until Objrst.EOF
.AddNew
For J = 0 To 1 ' Product + Location
.Fields(J) = Objrst.Fields(J)
Next J
For J = 2 To i ' permuted fields
.Fields(J) = Objrst.Fields(2 + i - J)
Next J
.Update
Objrst.MoveNext
Loop
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

Thanks PHV.

How would I go about If I need to create a table on the fly in VBA code with number of fields equal to the number of fields returned by my crosstab query.


I think I need to create a table on the fly because my crosstab query doesn't always return the same number of fields in the out put. Sometimes it returns 2, sometimes 3 and sometimes 10. So I like to create a table that has exactly the same number of fields that my cross tab query returns.

I hope I make it clear above. Do you know the rough syntax for accomplishing this. Looks like i need to use the table definition object but have no clue about the syntax.

Thanks a lot for your help

Jay

 

[PHV]

Or build on the fly a CREATE TABLE DDL instruction you execute before opening Objrst2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Jawad77]

Thanks a lot PHV