I have successfully combined the data from two tables with the same sort of data with a union join within the same database, but is there any way to union join two different database's and there tables? My database is getting far too big and I need to split the database but still be able to report on all of the data from both databases. I may also need to pull this information into a crystal report and be able to see it through crystal enterprise.

Many thanks

Try adding a linked server.

Please look up references to linked servers and Distributed queries on Books On Line.

I hope this helps.

sellis may be using only Access.
Yes there is a way, like this:
'----------------------------------
SELECT Employees.FirstName From Employees IN 'C:\db1.mdb'
union
SELECT Employees.FirstName From Employees;
'----------------------------

just make sure to use unoin or union all properly.

Remember that breaking up your data may have it's dangers. Try to move away from access and use at leats MSDE, preferably SQL Server. You could also create another database to denormalize your data and use it just for reporting. Repopulate it nightly from scheduled job from OS.



Sal

The easy way is to link the tables (File Menu, Get External Data, Link Tables) from whatever locations and then write the query in the database in which they are linked (and/or reside). Watch out for memo fields.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]