Combining/Consolidating DataBases

[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.

 

[Arvin Meyer [MVP]]

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

 

[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]]

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