Results 1 to 5 of 5
  1. #1
    ExodusNZ is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2

    Create Query Between Multiple mdb Files

    Hi guys

    Have been using Access for many many years (basic level) at work

    Our POS was designed in Access and all our database is in our MDB file

    Long story short, over the years the business has changed and we now have 4 different MDB files for each store (all set up structurally the same)

    It basically works by using the POS File.MDB file which is a small program that connects to ShopData.mdb and we are able to add new customers/and do everything we need to for the business)


    Sample Tables
    T_Customer
    T_Stock
    T_Transactions


    etc

    However to access the backdoor of the data we press F11 and create queries

    I now get sent ShopData from each shop every night and then download and rename them to each shop name as below:

    Sample Databases
    Shop1.mdb (Emailed to me named ShopData.mdb)
    Shop2.mdb (Emailed to me named ShopData.mdb)
    Shop3.mdb (Emailed to me named ShopData.mdb)
    Shop4.mdb (Emailed to me named ShopData.mdb)

    To access each database I need to paste the new ShopData.mdb into the root folder of the POS File.MDB and open it.

    Then to access another shop , I just rename or delete the ShopData.mdb to something else and copy another shops data

    Very messy - What I am wanting to do is make a query inside all 4 shops linking T_Stock

    Is something like this possible ?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Your structure may be convenient in the short term but not the best way to do this.

    You should have a 'master db' and import the data from each shop. However I suspect this would not be straightforward as you will have clashes on PK's etc.

    It also depends on how you handle stock codes and the like - each store stocks the same biscuit, but store1 calls it biscuit1, store2 calls it newbiscuit, etc

    But the short answer would be to use a new db and link to each of the 4 store files and create a union query

  3. #3
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I never used it but maybe you will find it interesting:
    https://support.office.com/en-us/art...0-689ba00a48e0

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    That feature was deprecated in 2013 or 16

  5. #5
    ExodusNZ is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    2
    Quote Originally Posted by Ajax View Post
    Your structure may be convenient in the short term but not the best way to do this.

    You should have a 'master db' and import the data from each shop. However I suspect this would not be straightforward as you will have clashes on PK's etc.

    It also depends on how you handle stock codes and the like - each store stocks the same biscuit, but store1 calls it biscuit1, store2 calls it newbiscuit, etc

    But the short answer would be to use a new db and link to each of the 4 store files and create a union query
    Thank you - I will google how to use this union query

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

Similar Threads

  1. Replies: 1
    Last Post: 04-10-2019, 11:39 AM
  2. Access Query - Export to multiple Excel files
    By WhosUsingMyName in forum Access
    Replies: 3
    Last Post: 05-22-2015, 03:53 PM
  3. Export one query to multiple Excel files
    By bliffer in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2014, 02:37 PM
  4. Replies: 1
    Last Post: 09-13-2013, 12:00 PM
  5. Replies: 11
    Last Post: 12-20-2012, 12:30 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