Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Consolidation (2003)

    I need some advice please on consolidating databases

    I have 3 separate divisions which are accounted for using 3 separate access databases, each of which has an identical file structure. The reasoning behind this is that 3 seperate groups need to do their work in parallel and sometimes off line.

    I now need to consolidate the databases on a monthly basis and join related tables through union. Given that there are 20 tables in each database I am concerned that a manual process will be time consuming and prone to error. Can somebody please advise on the best way forward?

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

    Re: Consolidation (2003)

    You can create a central database and use File | Get External Data | Link Tables to create links to the tables in the three databases.
    Access will name the linked tables automatically, but you can change the names to reflect the database they come from, for example by a prefix before the name. You will have to do this only once.
    Next, create union queries to combine related data from the three databases.
    You can use these as basis for further queries, forms and reports.

    Note: If you prefer to work with local data, or if the union queries are very slow, you can create make-table queries based on the union queries, and use the resulting tables instead of the union queries. You'd have to re-run the make-table queries from time to time to keep the local tables up-to-date.

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Consolidation (2003)

    Thanks Hans - I will try that

Posting Permissions

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