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:7/29/09 11:32 AM


Details

Often 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 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? Please add it below. Your name and tip will appear at the end of the document text.
Your name:
Your email:
Hide my email address
Verification code:
Enter the verification code you see above more submitting your tip
Tip:Please limit tips to 1000 characters

Navigation

Social Media

Powered by 10MinutesWeb.com