Combine 10 mdb files with different fields but common emp codes

[itsyash]

Hi,

I am pretty new to ms access. I want to combine fields for different
tables from ten ".mdb" files into one mdb file where all tables in
these mdb files have an employee code field.

The idea is to get the output in the single query as I plan to use
this query to form a pivot table in excel (which i can manage)

Can someone help me with:
1) The right approach to this (Is there a way to link these mdb files
or write queries etc?)
2) How to create these links. If they are queries, what will be the
syntax

Appreciate your help in advance.

-Yash

 

[Tom van Stiphout]

On Wed, 15 Oct 2008 19:02:47 -0700 (PDT), (e-mail address removed) wrote:

In your master MDB, create linked tables to the 10 other MDBs. Then
write a union query that combines these 10 tables to a single query.
Use it as a basis for your pivot.

-Tom.
Microsoft Access MVP

 

[a a r o n . k e m p f]

wow.. are you sure that each MDB file only has one table?

it seems like you might need a set of intermediary queries to
transform the data before allowing Excel to see the data.

I generally would use a real ETL tool like DTS or SSIS in order to get
the data in one database; and then join together the tables that you
need into a single SQL query.. and then link to the table.

it's just really a question of whether all 10 tables look the same or
if they're different.

 

[Tony Toews [MVP]]

(e-mail address removed) wrote:

Note that Aaron's reply mentions tools that are only available within
SQL Server.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/