Arvi Laanemets wrote:
All in vain! I put 2 queries into same sheet, and from another sheet
queried the result table to consolidate it (remove empty rows). On my
computer it works, on another asks for source again :-(((
Do you need to persist the summary table? In database terms this would
be denormalization The usual approach is to define a VIEW that
summarises the tables and query the VIEW.
VIEWs are not supported in Excel, of course. You could use the UNION
queries as a derived table in other queries, preferably with the
connection details in the query text itself e.g.
SELECT DerivedTable.key_col FROM (
SELECT CustID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Customers$]
UNION
SELECT EmpID AS key_col, lname FROM
[Excel
8.0;Database=\\MySserver\MyShare\MyWorkbook.xls;].[Employees$])
AS DerivedTable
ORDER BY DerivedTable.lname;
If you require the performance of a VIEW, or even a denormalized table,
you could consider defining it in a Jet .mdb file (a.k.a. 'Access
database' however the MS Access app is not a requirement) in the share.
You could subsequently consider moving the *data* into the database ...
<g.
Jamie.
--
|