|
|
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: | 7/29/09 11:32 AM |
|---|
DetailsOften users have to perform JOIN operations across multiple databases in MS Access. This tip show 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 that 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 allow 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 commentsPosted by fathi on 1/4/10 10:45 AM this good code i hope to useful from it
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? Please add it below. Your name and tip will appear at the
end of the document text.
|
