Results 1 to 6 of 6
  1. #1
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451

    linked databases query

    Hello all and in advance thank you once again for your help.
    i have several databases on a shared network, one for each location in our buisiness unit. what i am forseeing is that for the lead in this department i was thinking of giving a database that is linked to each individual database, that part is simple. my problem comes with the queries. each database has three tables lets say. they track the shift in the first, the object consumed in the second and the objects created from the first master object in the third. this works great in the individual databases because i have the ID's set up and it all links great in the queries. if i link all the different databases into a common database for the lead to be able to monitor the days activity how do i work the queries since each is identical just from a different location. surely someone here is currently doing this and has some do's and don'ts.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,495
    I would likely have used a single database, with a field in the table for location. Your solution is probably a UNION query to pull all the data together, which basically puts in the the same format (ie it normalizes the data).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    i used a make table query to add the location to each table but i'm unfamiliar with union queries. how do i use a union query? what i envision is to bring all the tables for shifts brought together with the individual locations attached, all the consumed brought together with the locations etc.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,495
    SELECT Field1, Field2, "Location1" As Location
    FROM Location1Table
    UNION ALL
    SELECT Field1, Field2, "Location2" As Location
    FROM Location2Table
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    vicsaccess's Avatar
    vicsaccess is offline Competent Performer
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    451
    ok, i love this forum, you guys give me enough information to get me thinking and researching new things i havent tried. so what i ended up with was using a query for each location adding to location code to each table. used the SQL code from each of those to copy and paste into a union query. looks great. thanks for the idea pbaldy.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,495
    No problem. A UNION query is a common workaround to a non-normalized design.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-09-2015, 07:20 PM
  2. Replies: 4
    Last Post: 10-02-2014, 12:30 PM
  3. Replies: 5
    Last Post: 02-02-2012, 06:42 PM
  4. Split Multiple Linked Databases
    By sifar786 in forum Database Design
    Replies: 0
    Last Post: 12-11-2011, 03:26 AM
  5. Converting/Upsizing Linked Databases
    By Chaz88 in forum Import/Export Data
    Replies: 2
    Last Post: 08-11-2010, 06:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums