Appending queries without refreshing all of them...

ColdCache

New member
Joined
May 13, 2016
Messages
1
Reaction score
0
Points
0
I have a large database with data from 2000 - 2016. Query#1 handles historic data from 2000 - 2015, and does not need to be refreshed. Query#2 handles current year 2016, and is refreshed weekly.

Is there a way to combine the two queries (so I end up with one table in the data model), but only have the data from Query#2 being refreshed? When I used Append on the two queries, it refreshed ALL of the data (and took quite a bit of time).
 
My usual approach to this is put Query #1 in a separate workbook and have it load to a sheet. Then I have Query #2 in my main workbook and it loads data from the sheet in the other workbook - ie a static version of Query#1.

You could do the same thing within one workbook. Basically have a Query#1 static which loads data from a sheet that the normal Query#1 has loaded to.

Does that make sense?
 
I have a large database with data from 2000 - 2016. Query#1 handles historic data from 2000 - 2015, and does not need to be refreshed. Query#2 handles current year 2016, and is refreshed weekly.

Is there a way to combine the two queries (so I end up with one table in the data model), but only have the data from Query#2 being refreshed? When I used Append on the two queries, it refreshed ALL of the data (and took quite a bit of time).

Short answer is no. The Append query basically looks at each data set and sees them as precedent calculations. As it can't tell that the data hasn't changed, it forces a refresh to make sure everything is up to date.

The suggestion to load to another workbook is probably the easiest workaround. In this way you only have to run the transformation portion of your retrieval once per year. In the weekly refresh, you'll still refresh all the data, but because it's in another workbook the recalc won't be triggered. So the refresh still runs, but if you shift off the transformation code you're basically just retrieving (and not manipulating) the data.
 
Back
Top