ArtofExcel
New Member
- Joined
- Jul 5, 2016
- Messages
- 17
Hey everyone,
I'm attempting to make copies of a document I'm already using but each copy will pull different rows out of a master "source" document. For example the document I already have uses Microsoft Query pulls rows with strings like "04 TestA", "05 TestB", "06 TestC" in one column and strings like "ABC" or "BCD" in another column. This returns all rows with any combinations of those strings in those two columns. The command text for it is as follows:
WHERE (`'Source Table$'`.Column_Name1='04 TestA') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='05 TestB') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='06 TestC') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='04 TestA') AND (`'Source Table$'`.Column2='BCD') OR (`'Source Table$'`.Column_Name1='05 TestB') AND (`'Source Table$'`.Column2='BCD') OR (`'Source Table$'`.Column_Name1='06 TestC') AND (`'Source Table$'`.Column2='BCD')
Now for the new documents I want to change which rows are pulled to those that have "01 TestX" in Column_Name1 with the same strings as before in Column2. I do not want to rebuild the queries from scratch as the document is complex and the the queries pull into tables that resize based on how many rows are found and includes extra columns with other information pulled from other parts of the file as well. Rebuilding each table would be very time intensive and involve fixing a lot of named references, ect.
I've tried changing the command text directly to what I want but I then get a ODBC error saying the query has "to few parameters". Is there anyway to make these changes without rebuilding the query from scratch? (I have only ever used the wizard to set up new connections), when I attempt to use the "Edit Query..." button I get an error saying "This query cannot be edited by the Query Wizard". I also have 2 other tables in the same file that are fed data via Queries from the original Query to the source file (the source file holds a lot of data so I only referenced it once in the file and built the rest of the document from that one reference, my hope is these other tables will automatically adjust if I can rebuild the one reference query without demolishing it).
I'm sure this sounds confusing so please ask for any clarifications needed.
I'm attempting to make copies of a document I'm already using but each copy will pull different rows out of a master "source" document. For example the document I already have uses Microsoft Query pulls rows with strings like "04 TestA", "05 TestB", "06 TestC" in one column and strings like "ABC" or "BCD" in another column. This returns all rows with any combinations of those strings in those two columns. The command text for it is as follows:
WHERE (`'Source Table$'`.Column_Name1='04 TestA') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='05 TestB') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='06 TestC') AND (`'Source Table$'`.Column2='ABC') OR (`'Source Table$'`.Column_Name1='04 TestA') AND (`'Source Table$'`.Column2='BCD') OR (`'Source Table$'`.Column_Name1='05 TestB') AND (`'Source Table$'`.Column2='BCD') OR (`'Source Table$'`.Column_Name1='06 TestC') AND (`'Source Table$'`.Column2='BCD')
Now for the new documents I want to change which rows are pulled to those that have "01 TestX" in Column_Name1 with the same strings as before in Column2. I do not want to rebuild the queries from scratch as the document is complex and the the queries pull into tables that resize based on how many rows are found and includes extra columns with other information pulled from other parts of the file as well. Rebuilding each table would be very time intensive and involve fixing a lot of named references, ect.
I've tried changing the command text directly to what I want but I then get a ODBC error saying the query has "to few parameters". Is there anyway to make these changes without rebuilding the query from scratch? (I have only ever used the wizard to set up new connections), when I attempt to use the "Edit Query..." button I get an error saying "This query cannot be edited by the Query Wizard". I also have 2 other tables in the same file that are fed data via Queries from the original Query to the source file (the source file holds a lot of data so I only referenced it once in the file and built the rest of the document from that one reference, my hope is these other tables will automatically adjust if I can rebuild the one reference query without demolishing it).
I'm sure this sounds confusing so please ask for any clarifications needed.
