Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select Query using tables from different databases (Access 2003)

    I have a table called T_Parsed located on my hard drive that contain fields Item_ID, Rev, MFR, and a few others. I also have a table called T_MFRAlias on our network drive that contains fields MFR, MFRAlias, and a few others. Is their a way that I could do an unmatched Select query against these two tables (fields: T_Parsed.MFR and T_MFRAlias.MFRAlias) without using the 'Link Table Manager...'?

    Note that I have tried to use the IN clause, however I can't get it to work.

    Thanks in advance for any help,
    Drew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Query using tables from different databases (Access 2003)

    What is your objection to importing or linking one of the tables into the other database?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Query using tables from different databases (Access 2003)

    If you really want to avoid linking or importing, you can do it like this:
    <code>
    SELECT T_Parsed.* FROM T_Parsed LEFT JOIN [H:FolderDatabase.mdb].T_MFRAlias ON T_Parsed.MFR = T_MFRAlias.MFRAlias WHERE T_MFRAlias.MFRAlias Is Null;
    </code>
    (Are you sure you want to join on MFR vs MFRAlias?)

  4. #4
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select Query using tables from different databases (Access 2003)

    Hans,

    I guess I'm trying to keep the local version of this database as small and stripped as possible and I was thinking that bringing more data from the network version to the local version may not be needed for what I'm trying to accomplish at this time (???). In addition, I don't know a lot about SQL's and I'm trying to learn more about them to determine what I can do when running into certain situations.

    As of today, I copy the network Alias table to the local database and then do the unmatched query to find all MFR's that are not currently in the Alias table. I guess after I do the unmatched query, I could delete the local table to keep the local database as small in size as possible? I'm not sure what would be most efficent?

    Regards,
    Drew

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select Query using tables from different databases (Access 2003)

    If you create a link to the network Alias table in your local database, it'll take up little space, since the data aren't stored in your local database. You can run a query based on a local table and a linked table without problems.

    If you import the network table, then delete it, you should compact the database (Tools | Database Utilities | Compact and Repair Database), for Access will not automatically reduce the size of the database after deleting objects or data. You can also set the database to Compact on Close in the General tab of Tools | Options... (this is a per-database setting)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •