MS Query - preserving column assignment when changing query

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,105
Office Version
  1. 365
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! :pray:
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,105
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,181,994
Messages
5,933,158
Members
436,882
Latest member
Kiddo456

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top