Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Combining/Consolidating DataBases

Discussion in 'Information Technology' started by smck, Jul 28, 2006.

  1. smck

    smck
    Expand Collapse
    Guest

    I have several databases that are not linked and would like to combine them
    so that queries and reports can be generated from each. Presently the
    information from each database is exported to Excel, Consolidated then
    imported back into Access. Is it possible to do this in Access without going
    through Excel.

    Any help is greatly appreciated.

    Thanks in advance.
     
  2. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    I'd create a new empty database and import all the tables from all the
    databases. If the table structure of any 2 or more tables are identical, you
    can then import the data into one of them and discard the other. If not,
    write queries to make the data conform to the same structure, then use a
    UNION query to combine the data.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "smck" <smck@discussions.microsoft.com> wrote in message
    news:0FC57667-AB54-460D-A236-80AB92E5124A@microsoft.com...
    > I have several databases that are not linked and would like to combine

    them
    > so that queries and reports can be generated from each. Presently the
    > information from each database is exported to Excel, Consolidated then
    > imported back into Access. Is it possible to do this in Access without

    going
    > through Excel.
    >
    > Any help is greatly appreciated.
    >
    > Thanks in advance.
    >
     
  3. BAC

    BAC
    Expand Collapse
    Guest

    I'm not an MVP, but my question is why aren't they linked? I would think
    it'd be easier and more straightforward to create a new front end (Database),
    link all the tables (if they're not linked -> then link them!) and use
    specific Union Query SQL to combine the data.

    eg:

    Select Cust, Add, phone
    from North_Table
    union Select Customer, Address1, Telephon
    From South_tbl
    Union Select Cus_name, Add_Line_1, phone
    From East_tbl

    which would work regardles of the table structure (subject to field type
    limitations of course which may require a conversion function).

    In addition to meeting the stated needs, it would avoid any changes to the
    existing datbases and problems with existing queries, reports, etc. and
    eliminate the need to "re-import' the tables from the old databases when the
    data changes.

    BAC


    "Arvin Meyer [MVP]" wrote:

    > I'd create a new empty database and import all the tables from all the
    > databases. If the table structure of any 2 or more tables are identical, you
    > can then import the data into one of them and discard the other. If not,
    > write queries to make the data conform to the same structure, then use a
    > UNION query to combine the data.
    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    > "smck" <smck@discussions.microsoft.com> wrote in message
    > news:0FC57667-AB54-460D-A236-80AB92E5124A@microsoft.com...
    > > I have several databases that are not linked and would like to combine

    > them
    > > so that queries and reports can be generated from each. Presently the
    > > information from each database is exported to Excel, Consolidated then
    > > imported back into Access. Is it possible to do this in Access without

    > going
    > > through Excel.
    > >
    > > Any help is greatly appreciated.
    > >
    > > Thanks in advance.
    > >

    >
    >
    >
     
  4. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    I was assuming that a new database was to be constructed from the old ones.
    If the old ones are still in use, queries and linked tables are required
    instead of imported tables. Structure of data columns used in Union queries
    must be the same. Your example appears to be, but if one of the phone
    columns was numeric, instead of text, it would need to be converted to text.
    Even though a new structure would require rewriting existing queries on the
    old databases, it would give the advantage of keeping that data validated
    with the same rules and being editable, something Union queries cannot do.
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "BAC" <BAC@discussions.microsoft.com> wrote in message
    news:76599323-C6B6-4853-BEE3-E33AB395826B@microsoft.com...
    > I'm not an MVP, but my question is why aren't they linked? I would think
    > it'd be easier and more straightforward to create a new front end

    (Database),
    > link all the tables (if they're not linked -> then link them!) and use
    > specific Union Query SQL to combine the data.
    >
    > eg:
    >
    > Select Cust, Add, phone
    > from North_Table
    > union Select Customer, Address1, Telephon
    > From South_tbl
    > Union Select Cus_name, Add_Line_1, phone
    > From East_tbl
    >
    > which would work regardles of the table structure (subject to field type
    > limitations of course which may require a conversion function).
    >
    > In addition to meeting the stated needs, it would avoid any changes to the
    > existing datbases and problems with existing queries, reports, etc. and
    > eliminate the need to "re-import' the tables from the old databases when

    the
    > data changes.
    >
    > BAC
    >
    >
    > "Arvin Meyer [MVP]" wrote:
    >
    > > I'd create a new empty database and import all the tables from all the
    > > databases. If the table structure of any 2 or more tables are identical,

    you
    > > can then import the data into one of them and discard the other. If not,
    > > write queries to make the data conform to the same structure, then use a
    > > UNION query to combine the data.
    > > --
    > > Arvin Meyer, MCP, MVP
    > > Microsoft Access
    > > Free Access downloads
    > > http://www.datastrat.com
    > > http://www.mvps.org/access
    > >
    > > "smck" <smck@discussions.microsoft.com> wrote in message
    > > news:0FC57667-AB54-460D-A236-80AB92E5124A@microsoft.com...
    > > > I have several databases that are not linked and would like to combine

    > > them
    > > > so that queries and reports can be generated from each. Presently the
    > > > information from each database is exported to Excel, Consolidated then
    > > > imported back into Access. Is it possible to do this in Access without

    > > going
    > > > through Excel.
    > > >
    > > > Any help is greatly appreciated.
    > > >
    > > > Thanks in advance.
    > > >

    > >
    > >
    > >
     

Share This Page