MS Query - preserving column assignment when changing query

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,021
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,147,679
Messages
5,742,575
Members
423,738
Latest member
AshleyKitsune

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