Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 5th, 2004, 08:12 AM
Registered User
Join Date: Apr 2004
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Union joins in SQL

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
  #2 (permalink)  
Old April 5th, 2004, 10:37 AM
Friend of Wrox
Join Date: Nov 2003
Location: Lehigh Acres, FL, USA.
Posts: 625
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jemacc

Try adding a linked server.

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

I hope this helps.
  #3 (permalink)  
Old April 6th, 2004, 08:44 PM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Location: Clarksville, TN, USA.
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

sellis may be using only Access.
Yes there is a way, like this:
SELECT Employees.FirstName From Employees IN 'C:\db1.mdb'
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.

  #4 (permalink)  
Old April 7th, 2004, 12:25 AM
Authorized User
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts

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.

J├╝rgen Welz
Edmonton AB Canada

Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL - Help with Joins oldmuttonhead Classic ASP Databases 0 January 21st, 2006 05:02 PM
SQL Joins in XPath arcuza XSLT 0 March 16th, 2005 01:05 PM
What is the Difference between Union and Joins ramk_1978 SQL Server 2000 1 February 9th, 2005 11:06 AM
SQL Table Joins Dredd Classic ASP Databases 2 June 3rd, 2003 06:24 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.