WinSQL » Knowledge base
Document information
| Document ID: | 751 |
|---|
| Subject: | Cross joining multiple databases in MS Access |
|---|
| Creation date: | 7/29/09 11:32 AM |
|---|
| Last modified on: | 12/12/18 10:56 AM |
|---|
Details
Often users have to perform JOIN operations across multiple databases in MS Access. This tip shows how to do that.
Assumptions
- You have two files:
- c:\data\Sales2006.mdb
- c:\data\Sales2007.mdb
- Each file has a table called Sales with the following definition.
create table sales(
id counter primary Key,
sDate date,
totalSale money
)
|
- You create an ODBC DSN that either connects to one of these files or any other file.
Write cross-database joins
The ODBC driver for MS Access allows using full path of your MDB file
in the FROM clause. For example, you can type the following query in
WinSQL if you are connected to an MS Access database.
select *
from c:\data\sales2006.sales
|
The above query assumes the name of your
MS Access file is Sales2006.mdb and it is saved in c:\data folder.
Additionally, it contains a table called
Sales. Notice that you do not have to specify the file extension. The ODBC driver assumes the extension to be .MDB.
Since MS Access allows users to specify the complete path of an MDB file, I can write the following query in WinSQL.
select MONTH(s2006.sDate) as Month,
sum(s2006.totalSale) as `Total Revenue for 2006`,
sum(s2007.totalSale) as `Total Revenue for 2007`,
Format(((sum(s2007.totalSale) - sum(s2006.totalSale)) * 100) /
sum(s2007.totalSale), '0.00') + '%' as `Percent Change`
from c:\data\Sales2006.Sales s2006, c:\data\Sales2007.Sales s2007
where s2006.sDate + 365 = s2007.sDate
group by MONTH(s2006.sDate)
|
This above query will return the following results.
Month Total Revenue for 2006 Total Revenue for 2007 Percent Change
----- ---------------------- ---------------------- -----------------
1 470113.7972 471105.7972 0.21%
2 404529.6060 410899.6060 1.55%
3 413008.0757 408358.0757 -1.14%
4 439694.4223 430199.4223 -2.21%
5 465558.5491 478206.5491 2.64%
6 433521.6771 448506.6771 3.34%
7 446718.7846 465039.7846 3.94%
8 451183.0818 486523.0818 7.26%
9 436758.9378 475533.9378 8.15%
10 480649.1908 525444.1908 8.53%
11 438940.9272 486865.9272 9.84%
12 453393.0238 507643.0238 10.69%
12 Row(s) affected
|
User comments
Posted by Jiri on 11/22/12 11:08 AM
Hi, Thanks but how it works when I have the new accdb format?
Jiri
Add a comment to this document
Do you have a helpful tip related to this document that you'd like to share
with other users?
Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our
public forums to post questions.
