Excel2003: Import External Data and formatting/sorting

[glyrocks]

Background: I've got a single spreadsheet I'm using to track changes to certain fields in 7 separate Access databases. So, I have 7 DB queries using the Get External Data... tool dumping into a single sheet. The tables and fields have the same names and data types in each database.

I've got no problem importing and refreshing data to keep up with the changes, but I need to sort on a single field across all 7 queries. Using the SORT function as part of the query works fine for each individual query but, obviously, doesn't take into account the data returned for other queries. When I remove SORT from the query and sort on the column in Excel, my data is sorted until I refresh the data. At that point, my formatting stays the same, but my data rearranges itself to the original query order.

So, my question is: How do I maintain sorting results across all 7 queries and continue refreshing the data? Is there a better approach? I do need 7 separate DBs for now...

Thanks!

dylan

 

[SkipVought]

Hi,

Consider modifying your workbook as follows:

Put each of the 7 queries in a separate sheet.

Insert a new sheet for a Union All query that will join the 7 queries into one resultset.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]