MS Query - preserving column assignment when changing query

[Oaktree]

I have an MS Query that pulls several attributes, including what is called the transmission date (i.e. the date the entry was processed). When I try to edit the query to change (literally change, not add/delete) transmission date to transaction date (the date the entry actually occurred), one of the other fields is repeated, causing the columns to be off by one (what used to be column CG is now CH, etc.)

I am just going to edit query, double clicking on the field name returned, changing it to "transaction date", and doing the same for a criterion.

Any ideas as to what would be causing this or, better yet, how to fix it?

Thanks! ray:

 

[Oaktree]

bump?

 

[Oaktree]

I did figure this out if anyone experiences this bizarre occurrence in the future.

If you have columns of formulas in the middle of your query, you will need to first cut and paste your formula columns so that your queried columns are all next to each other (and ensure that your formula references). Then, right click on your query, go to data range properties, and uncheck preserve column sort/filter/layout. You can then edit your query.

Once you have edited your query and returned the results, format the table to your liking and recheck the "preserve column sort/filter/layout" option.

Apparently, if your query range is not contiguous and you have the "preserve sort..." option selected, editing the query could cause havoc to your data.

Cheers.

 

[timwangTM]

Re: MS Query - preserving column assignment when changing qu

Oaktree, hopefully you have more experience on using MS query, here would you pls look at following question and share any idea:

how to prevent MS query from changing the * to field names?

http://www.mrexcel.com/board2/viewtopic.php?t=125093