Multi use query tips needed

[SueK]

Hi! I've inherited someone's big-a**ed query and am being asked to do make updates as the users figure out what they need.

The query reads data from a linked file. That data comes from SAP and takes a couple of hours to download. The users would like to do small downloads and run the query without losing the monster data. Is there anyway I can point the same query to two different data sets so I don't have to make every change to both queries?

Any ideas on dealing with this appreciated!

 

[The_Doc_Man]

Is there anyway I can point the same query to two different data sets so I don't have to make every change to both queries?

No, unless you are prepared to dynamically relink the implied tables underneath that query, or unless you want to experiment with parameter queries for which the underlying table name is one of the parameters - and I won't swear that the latter approach works. Or if you want to step into a QueryDef object and diddle around with its Fields collection. None of those are guaranteed to work because they imply stuff about the structure of the underlying tables being EXACTLY the same for the "monster" data and the "small download" data.

Having said that, my question to you is this:

Can you provide at least SOME common portions that will never change? The last time I did something at all similar, I had some crazy WHERE clauses that affected something like about 20 different reports from the same data source. I prepared two text strings that contained my "SELECT field-list", "FROM table-list JOIN criteria-list" clauses in one string and my "WHERE pot-load-of-criteria-subclauses" clause in the other string. Only ever had to change the WHERE clauses so I did that. When I was done, I concatenated my WHERE clauses to the appropriate "SELECT ... FROM ... JOIN ..." cause and made that my query, which eventually became a .Rowsource for a control on a form or report.

I think if you could look at that way of isolating the problem, you might see that it is easier than you would have guessed to maintain multiple queries that way.

 

[SueK]

Hi Doc Man! Thanks for all the great input. I'm absolutely going to have to play with parameter queries to see if I can find a way to make this work, as that would be seriously cool!

In the mean time, I think I'll put into action some of your idea on managing the separate queries to get everyone up and running while I play!

I'm a contractor and expect to escape this nuthouse no later then June. I need to make sure I leave them with something really easy to manage and make changes to.