Column sequence changed in recordset built out of crosstab query

P

Pradeep

Hello,

I have created a crosstab query with the following structure:

A --> Datatype is Text
B --> Datatype is Text
C --> Datatype is Text (this is the column heading and it has values like
111-
111,122-122,144-144...so on)
D --> Sum(Sal) --> Named as Total.

The cross tab shows up perfectly in the order expected as follows:

A B 111-111 122-122 144-144 Total
a b 1000 2000 3000 6000 (Sum of the values in the row)
c d 1000 1000 1000 3000

When i right click on the query and export into excel, it comes out
perfectly with the Total column at the end.

But when i create a recordset (DAO.Recordset Or Just Recordset) out of the
crosstab and do a CopyFromRecordset into Excel the order of columns is
disturbed and Total column comes right after column B as shown below:

A B Total 111-111 122-122 144-144
a b 6000 1000 2000 3000
c d 3000 1000 1000 1000

I am not sure as to why this happens. Any thougths/ideas on how to fix it
will be highly appreciated. I need to do a CopyFromRecordset and then format
the data and cannot do a DoCmd.Outputto (which is giving the data in right
order).

Thanks in advance.

Pradeep
 
P

Pradeep

Hello Alex,

I appreciate your quick response but that would take a lot of time
considering the amount of data that my access database deals with. As an
immediate solution, i would try to use the make table command and create a
table with data which i could then paste into Excel using CopyFromRecordset.

Thanks,

Pradeep
 
P

Pradeep

FYI,

The resultant table that we get because of maketable does not work. Even the
result of that statement has the column position moved.

Any more ideas, Pleaaaaaaaaaaase!!!

For the moment, i wrote code to move the column back to the last position
(in the excel file) but would like to know the reason for this change in the
column sequence.

Thanks,

Pradeep
 
P

Pradeep

Thanks Alex,

Any ideas of what needs to be done to avoid CopyFromRecordset doing that?
Anyways it appears there aren't any respones for this question so i happy to
mark it resolved.

Thanks , Aelx!!!

Pradeep
 
P

Pradeep

Thanks, Alex!!!

However it would be interesting to note that even Maketable gives the same
results as well. So i think this problem is not related to CopyFromRecordset
alone.

Something is wrong at the query level with regards to the Crosstab query.

Thanks for the help!!!

Pradeep
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to select one record of each from a column 1
How to select one record of each from a column 2
Need help with a formula 2
Custom Calculation in a Pivot Table 1
How to output a textfile format in such design 2
is this even possible? 27
series of formulas to get to one formula 0
Reference function from an out of sort table 5

Top